MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Compare Two Lists


Posted by Sean Scott on August 11, 2000 10:36 AM

Hello,

Thanks for the great page. I am a total Excel newbie and have almost zero knowledge of how it works. However, I still need help. I am extracting 2 flat files of customer lists from a unix box and opening them in Excel 97 on windows 98. #1 List contatins all customers that have purchased from us, listing customer number, name and total sales. List #2 contains all customers in the system, with customer number, name and full address. What we want is to compare the two lists and have a 3rd list generated of just the buying customers, with their addresses etc, eliminating the customers that have not purchased or have a sales total of zero.

Your help would be greatly appreciated. An exe file would be great as again, i have zero knowledge here. Thank you


Posted by Celia on August 14, 0100 5:13 PM


Sean
Do you need a third list? What about just adding the addresses to list1? It can be done either way.
Also, it can be done either with a macro or with a worksheet formula.
For instance, assuming the lists are on the same sheet, the data starts in row 2, and the columns are :-

A - List1.Nbr
B - List1.Name
C - List1.Sales
F - List2.Nbr
G - List2.Name
H - List2.Address

Put the following formula in cell D2 and fill down as far as requred :-
=VLOOKUP($A2,$F$2:$H$1000,3,FALSE)

This formula would need to be amended to fit the format and location of your lists. Post again if you need assistance in doing this.

Celia