Forum Discussion
Find mismatches
I want a way to flag or highlight a cell in Column B if the content does not exactly match the content on the same row in Column A. My spreadsheet has hundreds of rows of data, and just a few mismatches, and I'd like an easy way to find them. Seems like Conditional Formatting would offer an option, but I don't see one. Is there a way to do this?
5 Replies
- Tracy7212Tin Contributor
I simplified all my data and removed all the offending numerals - I hope this won't be rejected. Harun, thank you, but I used Laura's instructions because they look simpler and I am not a sophisticated Excel user at all. I really need step-by-step help. Thanks in advance.
Here is my data:
Using Laura's instructions, I selected cells B2 to B16 and entered this formula:
=$A2:$A16<>$B2:$B16
and got these results as expected:
Then I highlighted cells B2 and C2 and right-clicked Insert then Shift cells down and got this:
The results I want would have no fill in B3 and B4. The rest of Column B would have the pink fill.
My goal is as I insert blank row(s) in Column B and C, the rule compares the values as they exist after the insertion. If it worked the way I want, the final results after I insert all the necessary blank rows would look like this:
Is there a way to do this? There are times I use the boilerplate duplicate value conditional formatting, and inserting blank rows successfully readjust all the ones below it. But this is a more complicated operation. Any help is much appreciated.
- Tracy7212Tin Contributor
I guess there was a forbidden number combo hidden in my data that caused it to be rejected, so here is bare-bones clean data:
- Tracy7212Tin Contributor
I have tried 3 times to post followup questions but I keep getting this: "Content Not Permitted, two-four-seven is not allowed in the community. Remove it and try again." (Except it uses the numerals, not the spelled-out numbers.) I don't know what it is that's triggering this denial, so I will describe what I want to do. I have Column A, ID numbers, Column B, the same type of ID numbers, and Column C, data that goes with the Column B ID numbers. The whole universe of ID numbers is in Column A. Most of those facilities have data for Column C, but some do not. I want to be able to spot the mismatches between Column A and B, and insert blank cell(s) in Column B and C such that Column A and B match. When I do that, I want the entire rest of Column B to automatically adjust to meet the rule, in which mismatches are highlighted by color fill. Does that make sense?
- LauraBennettIron Contributor
Yes—use a formula-based conditional formatting rule.
- Select your data in column B, for example B2, with B2 as the active cell.
- Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter:=NOT(EXACT($A2,$B2))
- Click Format, choose a fill color, and click OK.
Each B cell will be highlighted if it doesn’t exactly match column A on the same row. EXACT catches differences in capitalisation and spaces, including trailing spaces.
If you don’t care about upper/lowercase differences, use this instead:
=$A2<>$B2
Make sure the row number in the formula matches the first row of your selected range. These rules compare cell contents, not visual formatting such as font or fill color.
- Harun24HRSilver Contributor
There are few of a ways to do that. By CF
=$B2<>$A2To get Mismatch values list, you may try-
=FILTER(B2:B10,A2:A10<>B2:B10) =FILTER(B2:B10,COUNTIFS(A2:A10,B2:B10)=0)