Compare two sheets using one column as reference and display difference - Using dictionary method

[ChanchalSingh]

Hi, I am new to macro. I have a requirement where I need to compare 2 sheets. Functionality should work as below:

1. In sheet1 there is a unique id column and even in sheet2 we have unique id column
2. Assume Sheet1 has 30 rows of data and sheet2 has 50 rows of that.
3. Need to search each unique id from sheet1 and compare with sheet2, if unique id of sheet1 is found in sheet2 then that row data should be compared
4. If any cell data mismatch in that row then it should highlight in yellow/red in sheet1.(if possible show difference Sheet1:aaa nd Sheet2: bbb)
5. I am able to do this using for loop method but its taking too much time(30 min to process 10k records). So I want to achieve this using dictionary method to get results quickly.

Example:

Sheet1:
UniqueId ColA ColB ColC
Id1 10 aaa 10/10/2000
Id2 20 bbb 12/10/2000
Id3 20 ccc 10/11/2001

Sheet2:
UniqueId ColA ColB ColC
Id1 10 zzz 10/10/2000
Id4 20 ccc 10/11/2001
Id2 20 bbb 12/10/2000
Id3 10 ccc 20/11/2001

Ouput:
Sheet1:
UniqueId ColA ColB ColC
Id1 10 aaa 10/10/2000 ----> Id1 is matching with Sheet2 but ColumnB has mismatch so should be highlighted in sheet1(if possible show difference in same cell)
Id2 20 bbb 12/10/2000 ----> Id12 is matching with Sheet2 but no change in color as data is matching in both sheets for that id
Id3 20 ccc 20/03/2010 -----> Id3 is matching with Sheet2 but ColA and ColC data mismatch so should be highlighted in sheet1(if possible show difference in same cells)

Note: Id4 is not there in Sheet1 so we are not using it for any comparing and ignoring it.

 

[jskasango]

Hey, guy. I am still here.
Do you have anything to tell me?

Just a thousand thanks! I am very grateful! I can now have a weekend! My data rows run in the excess of 150,000!!!