I have two lists, one is of potential members and the other is of members, I need to extract all potential members by name with address. I am not sure which i should use excel or access. The tables are in Excel right now. If i am forgetting any info you need please ask.


Thanks

Hi rterrain03,

Depending on the data structure, you should be able to do this in Excel. Without seeing it though, no-one can say for sure how to go about the process.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Two spreadsheets both in this format
FirstName LastName Address


The two spreadsheets have some of the same people on both.
I need a list of people that are not on both

Thanks

In Excel, suppose you have the data on Sheets 1 & 2, with FirstName LastName Address spanning columns A:C on each, and you want to find which 'members' on Sheet 2 also appear on Sheet 1. In that case, you could use a formula in D1 on Sheet 2 like:
=SUM(IF(A1&B1&C1=Sheet1!A:A&Sheet1!B:B&Sheet1!C:C, 1,0))
and copy down as far as needed. This will put a 1 on all rows for which a match is found, and 0 for all unmatched rows. If you then sort the data, by column D, all the 1s (members) will be grouped together, as will all the 0s (non-members).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I used that formula but i got all zero's and no 1's .. I think it might only be checking 1 for 1, ie a1 against a1. I need it to check the entire list of names for any matches in the other list that may be in a different cell/row
ie:
sheet1
a1 -smith john 123 address

with
sheet2
e1 -smith john 123 address

Hi rterrain03,

You need to use the formula as an array formula. To do that, select it and press Ctrl-Shift-Enter. Then copy, etc., as before.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]