Matching of sheets locating the difference

[amit_gupta123]

Guys , I am going through large data and want to compare the data. One data contains manual entries and the other is generated through system. i want to compare automated data with manual entred data and locate the difference and find out the where the error was made. I am looking to find such fixed parameters and fields on the basis of which we can compare data. Pls help....firstly mentioning manual data

this value should equel to value in "system" sheet in col F ideally should be at 10.30% of col C If NO then value of Col D will be equal to Col C this value should equel to value in "system" sheet in col E applied on col C

SB4183171 04-May-11 77210 70000 7210.00 10.30% YES 7 CH.CONAUD 7721 10% 194J

SB4183171 04-May-11 77210 70000 7210.00 10.30% YES 7 CH.CONAUD 7721 10% 194J

SB7481223 07-May-11 16058 14558.50 1499.53 10.30% YES 7 CH.COURIER 321 2% 194C

SB7481223 07-May-11 77210 70000 7210.00 10.30% YES 7 CH.CONAUD 7721 10% 194J

SB7691558 07-May-11 21415 19415 1999.75 10.30% YES 7 CH.COURIER 428 2% 194C

SB4173830 13-May-11 129750 117634 12116.30 10.30% YES 7 CH.OFFBOY 2595 2% 194C

SB8842847 18-May-11 1025.13 929.4 95.73 10.30% YES 7 CH.TEL

SB8842847 18-May-11 1275.29 1156.2 119.09 10.30% YES 7 CH.TEL

SB8842847 18-May-11 4092.13 3710 382.13 10.30% YES 7 CH.TEL

SB872882 20-May-11 9375.5 8500 875.50 10.30% YES 7 CH.FACLTY 188 2% 194C

SB872882 20-May-11 11628 10542.4 1085.87 10.30% YES 7 CH.COURIER 233 2% 194C

SB872882 20-May-11 11550.61 10472 1078.62 10.30% YES 7 CH.COURIER 231 2% 194C

SB6637420 06-May-11 25126 22780 2346.00 10.30% YES 21 CH.FACLTY 503 2% 194C

SB6637420 06-May-11 174.89 158.55 16.34 10.31% YES 21 WELTH CH.TEL

SB6637420 06-May-11 51.97 47.11 4.86 10.32% YES 21 WELTH CH.TEL

SB6637420 06-May-11 86.49 78.4 8.09 10.32% YES 21 WELTH CH.TEL

SB3204758 12-May-11 22.51 20.4 2.1 10.29% YES 21 WELTH CH.TEL

SB3204758 12-May-11 24.39 22.12 2.28 10.31% YES 21 WELTH CH.TEL

SB3204758 12-May-11 85.88 77.85 8.03 10.31% YES 21 WELTH CH.TEL


Now system data...


7 0007CH0000AD 05-05-2011 SB5523358 0 15000.00

7 0007CH0000ST 05-05-2011 SB5523358 0 11273.00

7 0007CH0000ST 07-05-2011 SB7691558 0 1050.00

7 0007CH0000ST 07-05-2011 SB7691558 0 1470.00

7 0007CH0000ST 07-05-2011 SB7691558 0 1050.00

7 0007CH0000ST 07-05-2011 SB7691558 0 563.00

7 0007CH0000ST 07-05-2011 SB7691558 0 4875.00

7 0007CH0000ST 13-05-2011 SB4173830 0 450.00

7 0007CH0000ST 16-05-2011 SB6742948 0 418.00

7 0007CH0000ST 18-05-2011 SB8842847 0 418.00

7 0007CH0000ST 31-05-2011 SB1168680 0 6255.00

7 0007CH0000ST 31-05-2011 SB1168680 0 1560.00

7 0007CH0000ST 31-05-2011 SB1168680 0 1333.50

7 0007CH0000ST 31-05-2011 SB1168680 0 2415.00

7 0007CH0000ST 31-05-2011 SB1168680 0 1260.00

7 0007CH0000ST 31-05-2011 SB1340381 0 122.00

7 0007CH0000ST 31-05-2011 SB1340381 0 540.00

7 0007CH0000ST 31-05-2011 SB1340381 0 700.00

7 0007CH000ENT 07-05-2011 SB7481223 0 1920.00

these are just extracts but u can change format etc to higlight matching and difference

 

[Hui]

Amit


You can simply add a =COUNTIF(Sheet1!$A:$A,D2) equation where

will refer to the column of

SB4183171

SB4183171

SB7481223

SB7481223


and D2 will be on Sheet2 with a value of SB5523358


otherwise you can use some more detailed COUNTIFS to add extra field checks

 

[amit_gupta123]

thnx will try the same and get back to u