Highlighting data mismatches in tables by row

[angeloudaki]

I have tried

=$A1<>$B1

but this only looks at two cells at a time.

=$A1:A5<>$B1:B5

does not work.

Ideally I would like Excel to check for differences between two tables row by row and highlight them. If not then at least return True/False for a range. I have a lot of data to sift through! I have searched online and again no luck. I also tried 'GoTo special' but it picked up cells which matched too. It seemed a completely random result.

 

[mole999]

you can do a simple in C =IF(A1=B1,1,"") and drag down, then filter on 1 or blank. It can be done in CF as well

 

[angeloudaki]

Thanks mole999 but that does the same as

=$A1<>$F1

. I need a code which will check data across the entire row.

[TABLE="width: 720"]
<tbody>[TR]
[TD="width: 80"]TABLE A>[/TD]
[TD="class: xl66, width: 80"]A[/TD]
[TD="class: xl66, width: 80"]B[/TD]
[TD="class: xl66, width: 80"]C[/TD]
[TD="class: xl66, width: 80"]TABLE B>[/TD]
[TD="class: xl66, width: 80"]F[/TD]
[TD="class: xl66, width: 80"]G[/TD]
[TD="class: xl66, width: 80"]H[/TD]
[TD="class: xl66, width: 80"]Check[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl66"]Yellow[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl66"]Banana[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Yellow[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl66"]Banana[/TD]
[TD="class: xl66"]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl66"]Green[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]Apple[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Green[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]Cherry[/TD]
[TD="class: xl66"]No[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl66"]Red[/TD]
[TD="class: xl66"]50[/TD]
[TD="class: xl66"]Cherry[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Red[/TD]
[TD="class: xl66"]0[/TD]
[TD="class: xl66"]Apple[/TD]
[TD="class: xl66"]No[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl66"]Blue[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Berry[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Blue[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Berry[/TD]
[TD="class: xl66"]Yes[/TD]
[/TR]
</tbody>[/TABLE]


So, in the table above, I need it to show that in Table B, cells H2 and G3:H3 are a mismatch to data in the same rows for Table A. The results in the 'Check' column use

=$A1<>$F1

and are based upon one pair of cells at a time so for example, checking A1 and F1, and so on. This is not great when I have 30 columns per table. The formula you gave just does the same but in a slightly different way.

 

[mole999]

Off the Wall
in I =IF(A1&B1&C1=F1&G1&H1,"Yes","No")

 

[Marcelo Branco]

To highlight the cells that don't match, try
Select F1:H4
In Conditional Formatting pick the Formula option and use this formula (without $)
=A1<>F1

Hope this helps

M.

 

[angeloudaki]

Off the Wall
in I =IF(A1&B1&C1=F1&G1&H1,"Yes","No")

Thanks... this works a treat however, my actual sheet has about 20 columns each table so I would have to type in &A1&B1&C1&D1&... x20 etc - it will be a long formula. Is there a way around this? I assumed originally A1:C1 but that breaks the formula.

For example, I also tried

=IF(Table10[@]=Table2[@],"Yes","No")

but that too fails.

 

[Marcelo Branco]

Thanks... this works a treat however, my actual sheet has about 20 columns each table so I would have to type in &A1&B1&C1&D1&... x20 etc - it will be a long formula. Is there a way around this? I assumed originally A1:C1 but that breaks the formula.

Try this generic formula where range1 and range2 are the same size
=IF(SUMPRODUCT(--(range1=range2))=COLUMNS(range1),"Yes","No")

Using your small data sample
=IF(SUMPRODUCT(--(A1:C1=F1:H1))=COLUMNS(A1:C1),"Yes","No")

M.

 

[angeloudaki]

To highlight the cells that don't match, try
Select F1:H4
In Conditional Formatting pick the Formula option and use this formula (without $)
=A1<>F1

Hope this helps

M.

Thank you - I have learned something new here too! Probably a silly question, but, how do I apply that to the entire table (250 rows and 25 columns per table)?

 

[Marcelo Branco]

Thank you - I have learned something new here too! Probably a silly question, but, how do I apply that to the entire table (250 rows and 25 columns per table)?

Same idea.
Select the entire Table2 and in CF use the formula that compares the first cell of Table1 with the first cell of Table2 without any $
Assumes Table1 and Table2 are the same size.

M.

 

[angeloudaki]

Same idea.
Select the entire Table2 and in CF use the formula that compares the first cell of Table1 with the first cell of Table2 without any $
Assumes Table1 and Table2 are the same size.

M.

Hmm, Works but when I amended A4 to "Pink", the CF was applied in F5 ! LOL... Weird. It's comparing -1 row...

[TABLE="class: grid, width: 646"]
<colgroup><col span="3"><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD="align: right"]6[/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yellow[/TD]
[TD="align: right"]6[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD="align: right"]5[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[TD]Red[/TD]
[TD="align: right"]5[/TD]
[TD]Cherry[/TD]
[/TR]
[TR]
[TD]Pink[/TD]
[TD="align: right"]10[/TD]
[TD]Berry[/TD]
[TD][/TD]
[TD][/TD]
[TD]Blue[/TD]
[TD="align: right"]10[/TD]
[TD]Berry[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD="align: right"]50[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD]Green[/TD]
[TD="align: right"]50[/TD]
[TD]Apple[/TD]
[/TR]
</tbody>[/TABLE]