I have two Excel spreadsheets. In the first spreadsheet I have nearly 20 columns and first column is unique ID. Once again Spreadsheet 2 has its own data of about 20 columns but only 4columns matches between this two speardsheets including similar unique ID column.
What I want to achieve:
Compare the column's that are similar in both the spreadsheet of Spreadsheet 2 against Spreadsheet 1.
say there is an update in spreadsheet1 in one of the common columns which is present in spreadsheet2 then it should update the difference and then highlight it after comparison.
To explain myself more, Say Spreadsheet 2, has 4 entries like 111,222,333,444 and suppose I want to validate the value for entry 111. Then my formula should search for 111 in Spreadsheet 1 Column A, then it should pick the data or value for it, then compare it with the value or data of the Spreadsheet 2 entry for 111, and then let me know if there is any update or changes made in any of the common columns after comparing.
can a vlookup function do this or can someone suggest some alternative for this..???
and can i perform this action through command line button on Spreadsheet1 (just by clicking the button it should perform the above task of comparison).
What I have tried:
I tried various VBA codes but in the end they either compare the cells range or add/delete new rows and columns. so I thought of starting it afresh and asking if someone can help me. I think there must me a solution for this problem somewhere as it's a basic problem with everyone who updates there records manually from different reports monthly/quarterly. Hope you understand.