vba code that compares 2 excel sheets.

[vidyasagarraov]

I need some assistance with a VBA code. I have tried different codes that i found in the forum but they didn't match with what i m looking for. So i will explain what i have , what i need and what i expect.

I have data in 2 worksheets "Sheet1 & Sheet2" . Both sheets have value from columns A to K and almost 10,000 rows.

What I want to do:

Sheet1 is reference sheet and changes have to be made in sheet2 based on values in Sheet1.

For example I have following data in sheet1
Name gender salary
charles Male 2000$
Rachel Female 1000$

Sheet2
Name gender salary
Rachel Female 3000$
charles Male 2000$

The reference should be taken as first cell of the row in sheet1, this is unique data, based on this value it has to check in the sheet2 where the row is and what value does it have.

The unique value is first cell in every row, but in sheet1 and sheet2 the value are jumbled left no easy way for me to compare.

Want I need:

Based on the data on sheet1 if there are any mismatches in sheet2, it should be hightlighted with some color.


Please help me guys I am in deep deep trouble

Thanks

[jforbes]

How often are do you need to do this, just once?
If so, you might want to attempt a Diff on the data.
This might work, depending on your version of Office.
Or you could look at WinMerge which is a great tool to compare two files or lists of text.

If you need to build a tool for this, I would first see if you could use Access to link to the two Excel tables then write a Query to find the discrepancies. Then possibly create a Report based off this Query to be used to update Excel.

Taking this further, you could import all the data into Access and develop whatever you need to replace the Spreadsheet permanently.

[NeoPa]

Just as a database system would, I suggest starting by sorting the data in both sheets by the unique reference. If it's necessary to maintain the original order then it's easy to create a new column first which you can populate with literal (Create with a formula but change to value before proceeding.) values to reflect the original order (1, 2, 3, ... 2319, 2320 etc).

Once you have two sorted columns then processing the data becomes much easier.