Help needed with Conditional Formatting

[vk7]

I am seeking assistance with applying conditional formatting to Row 2 of my spreadsheet. My goal is to compare values between Row 2 and Row 3 on a column-by-column basis. Specifically, I want to highlight any column in Row 2 where the corresponding values in Row 3 do not match.

For example:

Compare A2 with A3
Compare B2 with B3
Continue this comparison for all columns in Row 2 (A to BE)
I believe the EXACT function can be used to achieve this comparison. I attempted to test this by entering the formula =EXACT(A2, A3) in Row 4 for verification purposes. However, I encountered difficulties setting up the conditional formatting to highlight the relevant columns in Row 2 based on this comparison.

I would greatly appreciate any guidance on how to properly set up the conditional formatting rules to achieve this desired result.

 

[Chihiro]

So you want to apply CF to Cell which share same value?
Select A2:AN2.
Then use formula to format.
=A2=A3
Or you can use Exact as well.

 

[vk7]

Hello @Chihiro, thanks for helping.

I would like to have the CF to Cell which shares different value.. For example text can be same in Cell K2 "English" but in K3 if it is "English " then it should not match as it contains trailing space in K3 therefore it is should not be considered as exact match.

 

[Chihiro]

Then something like below. But note, it will only capture extra spaces causing difference.
=AND(K2<>K3,TRIM(K2)=TRIM(K3))

But in general, it's far better to apply data clean up to range. Rather than try and identify individual issues.

 

[vk7]

Good Point. But these data is exported from DB and I am importing as is.. Therefore I would like to find the discrepancies rather than trimming them in excel which hides the reality under the hood.

So without using the TRIM function, is there a way to achieve the results that I have asked for?

I guess most likely the below should work ?

=A2<>A3

 

[Chihiro]

Use TRIM. It won't alter stored value. It's just used in CF, to check if extra spaces are causing otherwise same text to be mismatch.

If you just use =A2<>A3. Then it will mark every cell that is different between row2 & 3. Ex: It will mark Married/Single as flag. Which I don't think you want.

Edit: If you wanted to check for partial match, but not full match. Then something like... This will handle extra char other than space.
=AND(K2<>K3,OR(ISNUMBER(SEARCH(K2,K3)),ISNUMBER(SEARCH(K3,K2))))