Compare two worksheets and identify common entries

[Guest]

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

 

[Guest]

Markt

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

 

[Guest]

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

 

[Guest]

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(A2,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,MATCH(A2&"",B!A:A,0)))
The A2&"" bit will convert A2 to text for correct matching


---