Comparing data with unique IDs

[ninkus]

Hi folks, I have a small problem here.

In a worksheet, I have 2 sheets. On sheet1, I have 5 columns, ID, NAME, COSTCTR, DESIGNATION, COUNTRY

In Sheet2, I have 5 columns too, LASTNAME, ID, PHONENUMBER, COSTCTR, STATUS.

Now, I need to use Sheet1 as my master data. I need to check every ID in Sheet 2 exist in Sheet1. If ID exist in Sheet2 and Sheet1, in Sheet1, I need to check that the costctr of that ID is the same for both Sheet1 and Sheet2.

ID is an unique alphanumeric thingy.

Look fwd to some help. Cheers

 

[Trebor76]

Hi ninkus,

Welcome to MrExcel!!

Assuming the data in both sheets is across columns A to E, try this formula in an empty column of Sheet2:

=IF(ISERROR(VLOOKUP(B2,Sheet1!A:C,3,FALSE)),B2&" is not in Sheet1",IF(D2<>VLOOKUP(B2,Sheet1!A:C,3,FALSE),"The COSTCTR for "&B2&" is different in Sheet1.",B2 & " is in Sheet1"))

You may have to tweak the three replies the formula produces to meet your needs.

Regards,

Robert

 

[ninkus]

Hi Robert,

Thanks for your help. I tried this method on the same simple setup as I quoted before. It works in giving me thr prompt that if the ID exist. Unfortunately, it doesn't resolve the corresponding COST CTR. I.e It doesn't tell me if the COST CTR of ID 1 in Sheet 1 matches the COST CTR of ID 1 in Sheet 2.

Cheers
Jeffery

 

[Trebor76]

It doesn't tell me if the COST CTR of ID 1 in Sheet 1 matches the COST CTR of ID 1 in Sheet 2.

Yes it does - the last comment i.e. B2 & " is in Sheet1" is for that

If COSTCTR is in column D simply change the B to D.

 

[ninkus]

Hi Roger, this is what I am seeing in the the last column when I put in the formulae. Sorry if I got anything wrong here. :_)

This is SHEET 1
ID Name Costctr Status
45 Jeff 111 g
48 MINSEE 222 g
50 CHRIST 333 g
14 WINNI 444 g


This is SHeet 2
LASTNAME ID PH COSTCTR STATUS
YAP 48 3253235 222 gh 48 is in Sheet1
I 50 235235 333 h 50 is in Sheet1
O 65 235235 555 h 65 is not in Sheet1
P 5 235235 686 h 5 is not in Sheet1

 

[ninkus]

Hey Roger, or anyone,

I have this formulae =MATCH(A36,'DATA'!E1:E3786,0)
With this, I manage to get the ROW number...

So normally, you will in a cell say =A15, where A15 is your data. But in this case, my 15 is derived from the formulae of =MATCH(A36,'DATA'!E1:E3786,0)??

 

[Trebor76]

this is what I am seeing in the the last column when I put in the formulae

So what do you expect to see??

I have this formulae =MATCH(A36,'DATA'!E1:E3786,0)

Where did the tab DATA come from?

So normally, you will in a cell say =A15, where A15 is your data. But in this case, my 15 is derived from the formulae of =MATCH(A36,'DATA'!E1:E3786,0)??

So what are you asking?? If you want to match column A with row result of your formula, you could use this:

=INDIRECT("DATA!A"&MATCH(A36,DATA!E1:E3786,0))

Robert