Comparing values of columns using a reference table

[nora28]

Hi,


I've tried looking around forums but couldn't seem to find the answer to this one. I'm currently trying to automate some testing by comparing two tables. The column headers are different so I have a third table that maps the column names. Example below :


Table 1 :


PersonalNumber Name Age

12 Mike 23


Table 2 :

ID Perso Date

Mike 12 2-3-12


Table 3 :

No Source Target

1 PersonalNumber Perso

2 Name ID


Basically I want to validate that the info in table 1 and 2 are the same (Personal Number = 12, Name = Mike), maybe highlight if different. Keep in mind that I'm mapping more than 1 column header in the third table and validating all these info (all the tables should have same info, just the column headers are different)


I've been looking at macros but can't seem to find any tha does this, most of them just compares cells statically. The position of the columns are not fixed so the code has to be dynamic. I'm newbie at macros and not that great at Excel formulas if you can't tell.


Thanks for any tips or help you might be able to offer.

Have a nice day,


Nora

 

[kchiba]

Hi

You can use VLOOKUP twice, once on table 1, looking up in table 2 and then one in Tabble 2 looking up in table1


if you columns are A:C on Table 1, in Column D you could put the formula

=VLOOKUP(B2,Table2!A:B,2,0)


on table 2 you could enter the formula

=INDEX(Table1!A:A,MATCH(Table2!A2,Table1!B:B,0))


The assumption in both cases is that your data is on row 2

 

[SirJB7]

Hi, nora28!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Give a look at this file:

https://dl.dropbox.com/u/60558749/Comparing%20values%20of%20columns%20using%20a%20reference%20table%20%28for%20nora28%20at%20chandoo.org%29.xlsx


There is a named range for each table and for second table an auxiliary or helper column for second table (to make suitable the function VLOOKUP) and a header table.


The three tables are differently shadowed and the test&check area too, all in different colors. If a match is not found its background becomes red and if it doesn't exist the font changes to yellow.


Hope it helps. Just advise if any issue.


Regards!