Compare the data

[anee_4285]

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

	A	B	C	D	E	F	G	H	I
1	Main Data	Main Data	Main Data	Main Data		Ref data	Ref data	Ref data	Ref data
2	Name	Fruit 1 <tbody> </tbody>	Fruit 2 <tbody> </tbody>	Fruit 3 <tbody> </tbody>		Name	Fruit 1 <tbody> </tbody>	Fruit 2 <tbody> </tbody>	Fruit 3 <tbody> </tbody>
3	Stephen <tbody> </tbody>	Apple <tbody> </tbody>	Banana <tbody> </tbody>	Blueberry <tbody> </tbody>		John <tbody> </tbody>	Blueberry <tbody> </tbody>	Banana <tbody> </tbody>	Apple <tbody> </tbody>
4	John <tbody> </tbody>	Blueberry <tbody> </tbody>	Banana <tbody> </tbody>	Apple <tbody> </tbody>		Stephen <tbody> </tbody>	Apple <tbody> </tbody>	Banana <tbody> </tbody>	Blueberry <tbody> </tbody>
5	Dave <tbody> </tbody>	Banana <tbody> </tbody>	Blueberry <tbody> </tbody>	Apple <tbody> </tbody>		Trevor <tbody> </tbody>	Banana <tbody> </tbody>	Blueberry <tbody> </tbody>	Apple <tbody> </tbody>
6	Trevor <tbody> </tbody>	Banana <tbody> </tbody>	Apple <tbody> </tbody>	Blueberry <tbody> </tbody>		Dave <tbody> </tbody>	Blueberry <tbody> </tbody>	Banana <tbody> </tbody>	Apple <tbody> </tbody>

<tbody>
</tbody>

Thanks in Advance.

Regards,
Anil

 

[Legacy 143009]

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")))

 

[anee_4285]

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.

 

[Nishant94]

Unknown
	A	B	C	D	E	F	G	H	I
1	Main Data					Check Data
2								Correct data
3	Name	Fruit 1	Fruit 2	Fruit 3		Check	Fruit 1	Fruit 2	Fruit 3
4	Stephen	Apple	Banana	Blueberry		TRUE
5	John	Blueberry	Banana	Apple		TRUE
6	Dave	Banana	Blueberry	Apple		FALSE	Blueberry	Banana	Apple
7	Trevor	Banana	Apple	Blueberry		FALSE	Banana	Blueberry	Apple
8
9	Ref data
10	Name	Fruit 1	Fruit 2	Fruit 3
11	John	Blueberry	Banana	Apple
12	Stephen	Apple	Banana	Blueberry
13	Trevor	Banana	Blueberry	Apple
14	Dave	Blueberry	Banana	Apple
15
16	TRUE means it matches and False means it need correction
17
Sheet6

Cell Formulas
Range	Formula
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)))

 

[anee_4285]

Unknown

	A	B	C	D	E	F	G	H	I
1	Main Data					Check Data
2								Correct data
3	Name	Fruit 1	Fruit 2	Fruit 3		Check	Fruit 1	Fruit 2	Fruit 3
4	Stephen	Apple	Banana	Blueberry		TRUE
5	John	Blueberry	Banana	Apple		TRUE
6	Dave	Banana	Blueberry	Apple		FALSE	Blueberry	Banana	Apple
7	Trevor	Banana	Apple	Blueberry		FALSE	Banana	Blueberry	Apple
8
9	Ref data
10	Name	Fruit 1	Fruit 2	Fruit 3
11	John	Blueberry	Banana	Apple
12	Stephen	Apple	Banana	Blueberry
13	Trevor	Banana	Blueberry	Apple
14	Dave	Blueberry	Banana	Apple
15
16	TRUE means it matches and False means it need correction
17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula 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))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

 

[Nishant94]

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

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.

 

[Legacy 143009]

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.

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...