Thread: [Solved] identify non-members from two lists
View Single Post
 
Old 11-30-2011, 09:14 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,343
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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]
Reply With Quote