Solved I Need To Match Data In 2 Columns

Asus M50vm-a1 15.4" notebook - core 2 du...
May 19, 2012 at 07:20:46
Specs: Windows 7, P8400, 4 GB
I have a document in Excel 2010 which contains 2 columns. Data from 2nd column is constant, does not change and is assigned to data in 1st column. Names and surnames are great example, so I will use this case for explanation.

Currently there are 200 unique names in 1st column and of course 200 surnames, attached to them in every row. There won't be any new names ever.

This is functionality I'd like to have. I often have to copy some of those names from external database (software) in various numbers. Let's say 50 random names to 1st column. But when I do that, I have to manually write down surnames to 2nd column, because they come from another database (software) which is not synchronized with the first one, so I cant just copy/paste them.

Is there any way to save (connect?) all 200 surnames to all 200 names in Excel, so when I would copy some of names from external database to 1st column, Excel would automatically attach appropriate surname to 2nd row?

I hope I was clear. Thanks for help in advance!


See More: I Need To Match Data In 2 Columns

Report •

✔ Best Answer
May 24, 2012 at 15:30:57
If I understand you correctly,
You want the number from Database Column B
to match up with the corresponding name from column A.

Try this:

On sheet January, Cell B5
and
On sheet February, Cell B5

enter the formula:

=VLOOKUP(A5,Database!$A$5:$B$12,2,FALSE)

Drag down

Your Sheets January & February
should now look like:

Sheet January
          A                   B
1) Name of company    constant numbers
2)
3)
4)
5) Lorem                       54
6) quisquam                    51
7) Dolor                       12
8) Porro                       32
9) Amet                        98

Sheet February
          A                   B
1) Name of company    constant numbers
2)
3)
4) 
5) Dolor                      12
6) Sit                        61
7) Amet                       98
8) Nequ                       71
9) Porro                      32

Any changes you make in Column A
will be reflected in a new number in Column B

Is that what your looking for?

MIKE

http://www.skeptic.com/



#1
May 19, 2012 at 08:27:13
You could probably use a =VLOOKUP() or some type of =MATCH() functions,
but we don't have enough info.

Post a small sample of your data, BUT first read this to learn how to post data in this forum:

http://www.computing.net/howtos/sho...

Will there ever be duplicates?
Using your name analogy IE:

Smith John
Smith William

MIKE

http://www.skeptic.com/


Report •

#2
May 19, 2012 at 13:22:37
No, there won't be any duplicates. All names are unique. Can I just upload xlsx document somewhere and post link?

Report •

#3
May 19, 2012 at 15:34:49
Can I just upload xlsx document somewhere and post link?

Unfortunately they do not have the facilities to upload to this site,
you can upload to another site and post a link,
or just copy a few lines from your sheet
and post them using the < pre > tags as outlined in the How To I posted.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 24, 2012 at 14:13:16
Sorry for the delay.

Here is a simplified example: http://dl.dropbox.com/u/18516025/ex... . I think it's pretty self explanatory. In the first tab, there is a constant database of all the companies and their assigned numbers. There are going to be several months (2nd and 3rd tabs are just for an example). I explained everything else in the first post.

Could you do a concrete example of my case and upload is somewhere, so I can take a closer look at it? Thanks in advance.


Report •

#5
May 24, 2012 at 15:30:57
✔ Best Answer
If I understand you correctly,
You want the number from Database Column B
to match up with the corresponding name from column A.

Try this:

On sheet January, Cell B5
and
On sheet February, Cell B5

enter the formula:

=VLOOKUP(A5,Database!$A$5:$B$12,2,FALSE)

Drag down

Your Sheets January & February
should now look like:

Sheet January
          A                   B
1) Name of company    constant numbers
2)
3)
4)
5) Lorem                       54
6) quisquam                    51
7) Dolor                       12
8) Porro                       32
9) Amet                        98

Sheet February
          A                   B
1) Name of company    constant numbers
2)
3)
4) 
5) Dolor                      12
6) Sit                        61
7) Amet                       98
8) Nequ                       71
9) Porro                      32

Any changes you make in Column A
will be reflected in a new number in Column B

Is that what your looking for?

MIKE

http://www.skeptic.com/


Report •

Ask Question