#1
|
|||
|
|||
identify non-members from two lists
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to identify the paragraph number you are on | SaneMan | Word VBA | 1 | 05-18-2011 05:43 AM |
sahred folder can't view by other members | mak101 | Outlook | 2 | 11-12-2010 12:58 PM |
Way to identify what image format has been inserted in PPT? | Pantucci | PowerPoint | 1 | 08-20-2010 12:03 AM |
how to identify the word files created using Windows or Mac Operating System? | mariaprabudass | Word | 0 | 08-06-2010 05:52 AM |
C# API to identify the uncommitted changes in Excel and Word document? | althafuddeen | Excel | 0 | 04-06-2010 07:40 AM |