help required in comparing 2 worksheet huge data

we have moved from our old access system to new sytem and we want to reconcile the data with the old system

below is old system

emp ded loan amt inst amt amt paid
1005 69 20,000.000 417.000 2,085.000
1005 54 44,750.000 250.000 11,250.000
1005 54 12,351.000 69.000 5,451.000
1005 54 10,740.000 60.000 6,180.000
1005 54 7,697.000 43.000 6,407.000
1006 54 38,843.000 217.000 13,888.000
1007 76 23,506.800 391.780 9,010.940
1007 54 8,592.000 48.000 5,280.000
1007 54 28,640.000 160.000 21,600.000
1008 76 19,562.400 326.040 7,498.920


below is new system
emp ded loan amt inst amt amt paid
1005 54 44750.000 250.000 11250.000
1005 54 12351.000 69.000 5451.000
1005 54 10740.000 60.000 6180.000
1005 54 7697.000 43.000 6407.000
1005 69 20000.000 417.000 2085.000
1006 54 38843.000 217.000 14105.000
1007 54 8592.000 48.000 5280.000
1007 54 28640.000 160.000 21600.000
1007 76 23506.800 391.780 9010.940


now i want to check both files to find out where there is mistake in new system
(all the fields to be checked)

can anyone help me out ??

thanks for the reply

i have removed the comma
but the problem is in the new data there are some emp codes missing how can i do now

e.g

old data
empcd dedcd loan amt instamt amt paid

1012 69 23096.400 384.940 1,154.820
1012 96 377.500 18.875 226.500
1012 96 755.000 37.750 453.000
1012 54 72000.000 400.000 7,200.000
1012 96 1971.000 49.275 1,823.175
1013 58 35.625 35.625 0.000


new data
empcd dedcd loan amt instamt amt paid
1012 75 25.650 50.000 0.000
1012 69 23096.400 384.940 1154.820
1013 58 35.625 35.625 0.000
1013 58 35.625 35.625 0.000
1014 54 5191.000 29.000 1943.000


if u see the 2 files old file has only 5 1012 emp code
where as new file has 2 1012 emp code and dd code is also not matching

i want to find the difference between 2 file

if there is any solution please let me know... i am stucked :-(

Originally Posted by FDibbins

if you want to compare 1 set of data to the other, 1st combine each set A1&B1&C1 etc, then use a vlookup from 1 to the other. Any error messages indicate a difference in data.
Or you could combine the vlookup with =iferror(vlookup(),"XX" )
you could then filter on that column, based on XX and see where the differences are

thanks for the reply

after combining the A1+B1+C1 I HAVE TO RUN VLOOKUP ON ALL THE CELLS???

can u further explain SORRY i am beginner in excel

1.jpg

wow that was great :-)
thanks a lot

1 more question kindly check the attached file

left side is old system data and blue coloured in new data

u can see the title i have sorted on the xxx

now is there any way i can get exact differnce in the cell ???

@FDibbins Sir i am waiting for your reply ???

how about this ?Compare.zip

lol through that addin its very easy

Thank akhilesh

lol through that addin its very easy

Thank akhilesh

Happy to help you.....

Please make the thread as solved if you got your answer

Originally Posted by newdoverman

this worksheet checks both lists against each other and checks the new list for duplicates within the new list.

thanks for the help this was also good one :-)


this is solved now


thanks everyone for your help