Common Entries Between Lists

[nielsfogt]

I have two extremely large lists and am looking for a way to quickly find common entries between the lists then input those entries into a seprate list.

Example - Take Common entries ("Company B" & "Company E") between List A & List B and place those entries into List C. I currently have the lists on seperate sheets.

List A
Company A
Company B
CompanyC
CompanyD
CompanyE
CompanyF

List B
Company 1
Company 2
Company B
Company 3
Company 4
Company E

List C
Company B
Company E

[oldchippy]

See if this helps?

[starguy]

I have two extremely large lists and am looking for a way to quickly find common entries between the lists then input those entries into a seprate list.

Example - Take Common entries ("Company B" & "Company E") between List A & List B and place those entries into List C. I currently have the lists on seperate sheets.

List A
Company A
Company B
CompanyC
CompanyD
CompanyE
CompanyF

List B
Company 1
Company 2
Company B
Company 3
Company 4
Company E

List C
Company B
Company E

Also see following attached file.

[nielsfogt]

these are both very helpful thank you! one question....is there a way to take the actual text from the new list C (IE - Company B and Company E) so that I can manage the list data....in other words, so that you are not dealing with the formula's but the actual text...the reason is that I will actually be taking this new list of common entires and going through this process a second time with another list.

[oldchippy]

these are both very helpful thank you! one question....is there a way to take the actual text from the new list C (IE - Company B and Company E) so that I can manage the list data....in other words, so that you are not dealing with the formula's but the actual text...the reason is that I will actually be taking this new list of common entires and going through this process a second time with another list.

If you copy column A on page 3, then paste special > values (into say column C), you can then select column C that will then allow you to sort column C in alphabetical order - delete any blank rows from column C.

[nielsfogt]

Old Chippy the formula you provided seems to work well...the other doesnt appear to function in the version of open office im using(dont have excel at work...)anyway....i have attached a file that shows my final issue....

when I do my second sort i am comparing multiple columns....you will see on sheet1 there are 3 columns (last, first, accountid) and sheet2 has 2 columns(last,first). I can combine the first and last name for matching purposes, but I would like for the account id to be shown on my new list on sheet3....is there an easy alteration to the formula to do this?

Thanks!!!!

[oldchippy]

Glad it worked for you. If you put the following formula on Sheet 3 in cell B1

=VLOOKUP(A1,sheet1!A1:C450,2,FALSE) this will match the first name in column 2

then put this in C1

=VLOOKUP(A1,sheet1!A1:C450,3,FALSE) this will bring in the ID number.

Where no match is found #N/A appears, if you need to show a blank instead, then modify the formula like so

=IF(A1="","",VLOOKUP(A1,sheet1!A1:C450,2,FALSE))