Excel 2007 : Advanced comparing two columns

[Moerz77]

Hi all,

I am trying for hours and just can't figure it out:

I have two worksheets:

1. Worksheet: about 5000 names Column A

2. Worksheet: about 2000 names in Column A and email adress in Column B

I want to compare the two columns A and insert the email adress of a found name in Column B of the 1. worksheet.

One further problem is that some names are there twice in the 2. worksheet with different email addresses.

Please help!

[NBVC]

So if there are duplicates, you want the other email adress displayed in column C?

Are there ever more than 2 matches?

[Moerz77]

Hey! No there should not be more than 2 matches and duplicated names should also have duplicate entries in email adresses in column A & B in the first worksheet.

[NBVC]

Ok since we are working with relatively large amount of data, we should use a helper column to alleviate processing stress....

So in Sheet2, column C add formula in C2:

=A2&"_"&COUNTIF(A$2:A2,A2)

and copy down

Then in Sheet1, use formula in B2:

Please Login or Register  to view this content.

adjust sheet name and ranges to suit and then copy to next column and down the whole list.

[Moerz77]

Hi,

thanks a million, this works in general, but some email-adresses appear twice in Sheet 1 and I noticed some names appear more than twice in Sheet 2 ;(

Could you help adjust the formular?

Thanks again for your quick support.

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Moerz77]

attached is worksheet with your formular and the result.

In general it works but not quite as needed

Problem description:Sheet 1: about 5000 names Column A
Sheet 2: about 2000 names in Column A and email adress in Column B

I want to compare the two columns A and insert the email adress of a found name in Column B of the 1. worksheet.
For every name that is found more than once in Sheet 2 there should be the appropriate amount of entries in Sheet 1

Problem with your formular: Only the first email adress of a duplicated name gets inserted into Sheet 1 column B

I hope this is understandable

[NBVC]

There are 6 Name1's in Sheet1 and 9 Name1's in Sheet2. Do you want only the first 6 matches of Name1 to be used in Shee1?

[NBVC]

If so, then:

=IFERROR(INDEX(Sheet2!$B$2:$B$6000,MATCH(A2&"_"&COUNTIF(A$2:A2,A2),Sheet2!$C$2:$C$6000,0)),"")

copied down

[Moerz77]

Hey! This seems to work great. Somehow in the 2000 results in sheet 1 there were 2 duplicate email addresses - Not sure why...maybe you have an idea?

Anyway, this was the solution to my problem! Thaaaaanks so much.