Compare two worksheets and identify common entries
 

Hi All,

I have two different worksheets that both contain a column called Customer
ID. SpreadsheetA contains info on a specific sales reps that belongs to a
specific cusomter ID. SpreadsheetB contains info on sales for the month
belonging to specific customer ID's. I would like to try to compare the like
column (customer ID) so that if there was a sale made to a customer from the
SpreadsheetA it would print the sale amount off of SpreadsheetB. This could
be listed in SpreadsheetB or a new one - that does not matter.

For a more visual explanation:

SpreadsheetA

Customer ID
1001
1005
1099
1255

there is other information in SpreadsheetA, in the other columns, but I am
only using info from SpreadsheetB if the Customer ID is listed in SpreadsheetA

SpreadsheetB

Customer ID Name Sale

1001 ABC Co. $500.00
1002 XYZ Co. $900.00
1255 SKL Co. $200.00
1333 ASD Co. $800.00

Looking to compare both Customer ID columns and printing the info if there
is a match:

1001 $500.00
1255 $200.00


I am using Excel 2007. I have looked in the archives but have not been able
to find my answer. Thanks for your anticipated help!

Mark

Compare two worksheets and identify common entries
 

Markt
"MarkT" wrote:

For a more visual explanation:

SpreadsheetA
column
.............A.........................B
1..........Customer ID
2..........1001
3......... 1005
4..........1099
5..........1255

there is other information in SpreadsheetA, in the other columns, but I am
only using info from SpreadsheetB if the Customer ID is listed in SpreadsheetA

SpreadsheetB
Column
...............A..................B..............C
1..........Customer ID.....Name........Sale

3..........1001...............ABC Co.......$500.00
4..........1002...............XYZ Co.......$900.00
5..........1255...............SKL Co.......$200.00
6..........1333...............ASD Co......$800.00

Looking to compare both Customer ID columns and printing the info if there
is a match:

2|... 1001 $500.00 with: =VLOOKUP(A2;SpreadsheetB!$A$2:$C$5;3;FALSE)

4|... 1255 $200.00 with: =VLOOKUP(A4;SpreadsheetB!$A$4:$C$7;3;FALSE)

it is very importend that the both coustomerID in SpreadsheetA and
SpreadsheetB have the same typ, p.e. both are text or both are number

I am using Excel 2007. I have looked in the archives but have not been able
to find my answer. Thanks for your anticipated help!

with regards
Marion

Compare two worksheets and identify common entries
 

Sorry Marion, that didn't seem to work. I am getting #NA errors.

"Marion Löwe" wrote:

Markt
"MarkT" wrote:

For a more visual explanation:

SpreadsheetA
column
............A.........................B
1..........Customer ID
2..........1001
3......... 1005
4..........1099
5..........1255

there is other information in SpreadsheetA, in the other columns, but I am
only using info from SpreadsheetB if the Customer ID is listed in SpreadsheetA

SpreadsheetB
Column
..............A..................B..............C
1..........Customer ID.....Name........Sale

3..........1001...............ABC Co.......$500.00
4..........1002...............XYZ Co.......$900.00
5..........1255...............SKL Co.......$200.00
6..........1333...............ASD Co......$800.00

Looking to compare both Customer ID columns and printing the info if there
is a match:

2|... 1001 $500.00 with: =VLOOKUP(A2;SpreadsheetB!$A$2:$C$5;3;FALSE)

4|... 1255 $200.00 with: =VLOOKUP(A4;SpreadsheetB!$A$4:$C$7;3;FALSE)

it is very importend that the both coustomerID in SpreadsheetA and
SpreadsheetB have the same typ, p.e. both are text or both are number

I am using Excel 2007. I have looked in the archives but have not been able
to find my answer. Thanks for your anticipated help!

with regards
Marion

Compare two worksheets and identify common entries
 

In sheet: A,

Assuming lookup cust id data in col A are consistent with those in sheet B's
col A
Put in B2, format B2 as currency, copy down:
=IF(ISNA(MATCH(A2,B!A:A,0)),"",INDEX(B!C:C,MATCH(A 2,B!A:A,0)))

If the lookup cust id data in col A might be/contain text numbers
while those in sheet B's col A are real numbers,
then try instead in B2:
=IF(ISNA(MATCH(A2+0,B!A:A,0)),"",INDEX(B!C:C,MATCH (A2+0,B!A:A,0)))
The A2+0 bit is one way to convert A2 to real number for correct matching

If the lookup cust id data in col A are real numbers
while those in sheet B's col A are text numbers,
then try in like this in B2:
=IF(ISNA(MATCH(A2&"",B!A:A,0)),"",INDEX(B!C:C,MATC H(A2&"",B!A:A,0)))
The A2&"" bit will convert A2 to text for correct matching

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---