Checking multiple values across cells and flagging it

[gmoorthy]

I have a huge file ( Excel) around 40,000 rows and the data in column A will get repeated only twice and for those duplicate values in column A i need to compare each values for that respective columns. For example

ColumnA ColumnB ColumnC
123 ABC MNY
123 XYZ MNY


So i for A1 and A2 i need to compare B1 and B2 , C1 with C2 and if B1 and B2 are not same flag both B1 and B2 with something like colour so it is indicated that it is different.

 

[SkipVought]

hi,

That should be a simple Conditional Format for each column that you want to compare.

I absolutely NEVER have tables of data that do not have headings. A heading row WILL be essential to this solution. Therefore your DATA will begin in Row 2.

Select the column containing the DATA that you will conditionally format. Only the DATA in that column!

Open the CF dialog and select a FORMULA to determine the cell format.

ALL formulas for ANY CF, will be referenced with respect to the TOP-LEFT cell of the selection.

You will want to reference column A, to determine where the values change from one row to the previous or to the next AND you will want to reference where the values in that column are NOT THE SAME for the couplet in column A. Might look something like this as A2 is the cell of reference...
[tt]
=OR(AND(A2=A1,B2<>B1),AND(A2=A3,B2<>B3))
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...and actually if you did your references making column a ABSOLUTE, you could use this formula for columns B:WHATEVER, in other words, select B2:WHATEVERlastrow and CF formula is for all rows & columns...
[tt]
=OR(AND($A2=$A1,B2<>B1),AND($A2=$A3,B2<>B3))
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}