Conditional Formatting to compare lists and order

[SarahBear]

I have a situation where I am comparing two lists that should be identical. I should note that the lists contain text, not numbers. I know that one is correct, and I am looking for errors in the second list. These can be spelling errors, or that an ingredient in the list is appearing in the wrong order within the list.

I know how to get it to highlight unique values within the two lists, which will show the spelling errors, but I need it to also highlight any ingredients that are out of order.



I know that this is possible to do with conditional formatting, I'm just not sure how.

I have attached a file with lists that contain the differences I want to highlight. Idealy, Cells A2, B2, A7, B7, A8, and B8 would be highlighted.

[gebobs]

When I am looking for similar list errors, what I do is write an equation in a third column that compares the first two, =IF(A1<>B1,"?",""), and copy it down the length of the list. I'v e been doing it that way for years so if there is a better way i would be interested in hearing about it.

[SarahBear]

Thanks gebobs. I'm actually looking to do it via conditional formatting so that I can record it in a macro and input different lists in the cells. I have found that I have to clear rules and run a new format every time, or else it won't revert formatting when I run the macro. I know it is possible to do because I have seen a former coworker do it, I just don't know he did it.

[SarahBear]

I figured it out!
Okay, you just highlight both lists, go into conditional formatting, new rule, "use a formula to determine which cells to format", then enter in the formula "=$A1<>$B1", set your formating however you want it to show the values that don't match, and hit ok.

[gebobs]

Originally Posted by SarahBear

I figured it out!
Okay, you just highlight both lists, go into conditional formatting, new rule, "use a formula to determine which cells to format", then enter in the formula "=$A1<>$B1", set your formating however you want it to show the values that don't match, and hit ok.

Lovely! I hadn't thought of that. Well done! That's what I'll do from now on.