Collate data from one spreadsheet into another spreadsheet
 

I have two spreadsheets. Spreadsheet A has a list of 500 clients. Spreadsheet
B has a list of over 5000 members.

I need to know if any of the 500 clients in Spreadsheet A appear in
Spreadsheet B, then collate this data in a new spreadsheet.

We can assume the members' first name and surname are in separate columns.
There is also a column with each member's account number.

Is there a formula that can locate and extract the data I need?

Thanks

Collate data from one spreadsheet into another spreadsheet
 

Suppose you have the members account number in ColA of both sheets; then try
the below in Sheet1 (500 records) which will check the membership number in
ColA of Sheet2 (5000 records)..and return the text "Found" or return
blank...Copy the formula down and once done you can sort the formula column
and copy paste the data to a different sheet...

=IF(COUNTIF(Sheet2!A:A,A1),"Found","")

If this post helps click Yes
---------------
Jacob Skaria


"Anthony" wrote:

I have two spreadsheets. Spreadsheet A has a list of 500 clients. Spreadsheet
B has a list of over 5000 members.

I need to know if any of the 500 clients in Spreadsheet A appear in
Spreadsheet B, then collate this data in a new spreadsheet.

We can assume the members' first name and surname are in separate columns.
There is also a column with each member's account number.

Is there a formula that can locate and extract the data I need?

Thanks

Collate data from one spreadsheet into another spreadsheet
 

Excel 2007 Tables
Extract data from 2 tables and consolidate.
No formulas or code used.
Ranges dynamic.
Assumes account# appears only once.
http://www.mediafire.com/file/mfgno0h0yte/10_19_09.xlsx