Compare the data

A

anee_4285

New Member
Joined
Jul 9, 2015
Messages
27
Hello All,

I want to compare two data's. My A-D are main data, F-I are my ref data. I have to check is the reference data have the same value or not. If it dont have it then it should provide correct data.

If you see Stephen have the same Fruits in both Main and Ref data. Some thing should come up that its correct
Dave dont have the right fruit. some where it should show that its wrong and give the correct data. So that i can easly copy paste it. It has to tell only G6 and H6 are wrong and **, ** are correct



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Main Data[/TD]
[TD]Main Data[/TD]
[TD]Main Data[/TD]
[TD]Main Data[/TD]
[TD][/TD]
[TD]Ref data[/TD]
[TD]Ref data[/TD]
[TD]Ref data[/TD]
[TD]Ref data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Fruit 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Fruit 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Fruit 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Fruit 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Fruit 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Fruit 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Stephen[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Stephen[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Dave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Trevor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Trevor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Dave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Blueberry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Banana[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Apple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in Advance.

Regards,
Anil
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try to paste it to J2

=IF(AND(NOT(INDIRECT("$B"&MATCH($F2,A:A,0))=$G2),NOT(INDIRECT("$C"&MATCH($F2,A:A,0))=$H2),NOT(INDIRECT("$D"&MATCH($F2,A:A,0))=$I2)),"G, H, I",IF(AND(NOT(INDIRECT("$B"&MATCH($F2,A:A,0))=$G2),NOT(INDIRECT("$C"&MATCH($F2,A:A,0))=$H2)),"G, H",IF(NOT(INDIRECT("$B"&MATCH($F2,A:A,0))=$G2),"G","NO PROBLEM")))
 
Last edited by a moderator:
Upvote 0
Hi ,

For 'Trevor' also it gave me 'No Problem', actually, my Main data and Ref is not matching for Trevor.
For Dave it gave me that G and H are wrong. Is there any way it paste the correct value in the next cell so that i can easly copy paste it.. I have around thousands of list. So if i get the difference. Still i have to do lot of manual work.

Thanks.
 
Upvote 0

Unknown
ABCDEFGHI
1Main DataCheck Data
2Correct data
3NameFruit 1Fruit 2Fruit 3CheckFruit 1Fruit 2Fruit 3
4StephenAppleBananaBlueberryTRUE 
5JohnBlueberryBananaAppleTRUE
6DaveBananaBlueberryAppleFALSEBlueberryBananaApple
7TrevorBananaAppleBlueberryFALSEBananaBlueberryApple
8
9Ref data
10NameFruit 1Fruit 2Fruit 3
11JohnBlueberryBananaApple
12StephenAppleBananaBlueberry
13TrevorBananaBlueberryApple
14DaveBlueberryBananaApple
15
16TRUE means it matches and False means it need correction
17
Sheet6
Cell Formulas
RangeFormula
F4=SUMPRODUCT(--(INDEX($B$11:$D$14,MATCH($A4,$A$11:$A$14,0),0)=$B4:$D4))=COUNTA($B4:$D4)
G4=IF($F4,"",INDEX(INDEX($B$11:$D$14,MATCH($A4,$A$11:$A$14,0),0),COLUMNS($G4:G4)))
 
Last edited:
Upvote 0
Nishant94 said:
Unknown
ABCDEFGHI
Correct data
NameFruit 1Fruit 2Fruit 3CheckFruit 1Fruit 2Fruit 3
StephenAppleBananaBlueberry
JohnBlueberryBananaApple
DaveBananaBlueberryAppleBlueberryBananaApple
TrevorBananaAppleBlueberryBananaBlueberryApple
NameFruit 1Fruit 2Fruit 3
JohnBlueberryBananaApple
StephenAppleBananaBlueberry
TrevorBananaBlueberryApple
DaveBlueberryBananaApple
TRUE means it matches and False means it need correction

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Main Data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Check Data[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Ref data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=SUMPRODUCT(--(INDEX($B$11:$D$14,MATCH($A4,$A$11:$A$14,0),0)=$B4:$D4))=COUNTA($B4:$D4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=IF($F4,"",INDEX(INDEX($B$11:$D$14,MATCH($A4,$A$11:$A$14,0),0),COLUMNS($G4:G4)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Thanks, but i have around 3000 names, so i cant have it at down. can i both the list sise to side or in different sheet
 
Upvote 0
anee_4285 said:
Thanks, but i have around 3000 names, so i cant have it at down. can i both the list sise to side or in different sheet
Click to expand...

Yes you can keep the reference range in different sheet. The benefit of the data setup I used is you can filter the false one to check those names that are not correct.
 
Upvote 0
anee_4285 said:
Hi ,

For 'Trevor' also it gave me 'No Problem', actually, my Main data and Ref is not matching for Trevor.
For Dave it gave me that G and H are wrong. Is there any way it paste the correct value in the next cell so that i can easly copy paste it.. I have around thousands of list. So if i get the difference. Still i have to do lot of manual work.

Thanks.
Click to expand...

OK then,
This will check the first column:
INDIRECT("$B"&MATCH($F2,A:A,0))=$G2

This will check the second:
INDIRECT("$C"&MATCH($F2,A:A,0))=$H2

And this will check the third:
INDIRECT("$D"&MATCH($F2,A:A,0))=$I2

Use them however you like...
 
Upvote 0
You must log in or register to reply here.

Similar threads

muhammad susanto
  • Solved
Determine Duplicate & Unique Based on List of Data
Replies
7
Views
476
awoohaw
awoohaw
2
  • Question Question
Renaming and hidding a sheet after taking value from a changing cell
Replies
9
Views
557
HaHoBe
HaHoBe
V
  • Question Question
run vb-application to search string in the table to "score"
Replies
0
Views
207
vic_it
V
P
  • Question Question
Compare tables and find closed date prior to selection
Replies
2
Views
531
Paradox_R
P
V
  • Solved
Compare data in three sets of columns, highlight non matches
Replies
9
Views
141
Eric W
Eric W

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top