Comparing 2 different Excel Worksheets & obtaining desired outcome

**rvkadu1** · 05-10-2011, 05:00 PM

Hi Excel Gurus,
I have 2 different Excel Worksheets (both worksheets having same format & columns LINE POD CONTAINER NO ISO IMO STATUS); I have to compare these different Excel Worksheets Column by column or field by field…
(2 different Excel Worksheets naming DATA_LIST & DATA_SYS)
Requirement:
1. All the Mis-matched Records should be reflected into Worksheet “Discrepancy”
a. Duplicate in EAL : Find out duplicates records in DATA_LIST
b. IMO Mismatch : To Compare IMO field - DATA_LIST vs. DATA_SYS
c. ISO Mismatch : To Compare ISO field - DATA_LIST vs. DATA_SYS
d. POD Mismatch : To Compare POD field - DATA_LIST vs. DATA_SYS
e. Line Mismatch : To Compare Line field - DATA_LIST vs. DATA_SYS
f. Status Mismatch : To Compare Status field - DATA_LIST vs. DATA_SYS
g. STOP LIST : Record(s) found in DATA_SYS but not in DATA_LIST
h. NOT IN YARD : Record(s) found in DATA_LIST but not in DATA_SYS

2. All the Matching Records should be reflected into Worksheet “Released List”
a. Released List : Match Records in DATA_LIST vs. DATA_SYS

Note :
1. All the Records should have Serial No.
2. In “Discrepancy” worksheet keep 2 rows blank in between 2 fields’ mismatches.
3. ISO Mismatch if matches in “EQ_ISO” worksheet , same record(s) should not appear as ISO Mismatch

4. If Mismatch or matching is Nil then only heading appear
I use to do this activities daily basis, please help me in this regards, It will very grateful if someone come forward and give me solution (macro etc) for the same.
(File is attached for your ready ref.)