Forum Discussion
How do I highlight odd/even occurrences of non-numerical data using Conditional Formatting?
Hey, I've combined two different lists of client transactions together that should--theoretically--show the same information. I need to quickly find discrepancies and reconcile differences betwee...
Surely this would be easier to do prior to combining the lists. Regardless, this is easier if you can split up the values, delimited with the pipe character, instead of having them concatenated together like your example here.
To break out the customer from your data, the formula would be:
=RIGHT(E2,LEN(E2)-FIND("|",E2)-1)
To utilize this (new helper) column, assuming you used column F as your helper column, your formula would then become:
=AND(ISODD(COUNTIF($F$2:F2,F2)),COUNTIF($F$2:$F$481,F2)=COUNTIF($F$2:F2,F2))
Enter in F2 (or wherever) and copy down.
You can also utilize this as a conditional formatting rule. Just ensure that when you select your data set you pay attention to what cell is the active cell, because of the relative referencing. If you apply that formula as a conditional formatting formula to range E2:E481, just ensure E2 is the activecell when entering it and you'll be fine.
If you don't want to split your data via delimiter it will become much, much more complicated.
To break out the customer from your data, the formula would be:
=RIGHT(E2,LEN(E2)-FIND("|",E2)-1)
To utilize this (new helper) column, assuming you used column F as your helper column, your formula would then become:
=AND(ISODD(COUNTIF($F$2:F2,F2)),COUNTIF($F$2:$F$481,F2)=COUNTIF($F$2:F2,F2))
Enter in F2 (or wherever) and copy down.
You can also utilize this as a conditional formatting rule. Just ensure that when you select your data set you pay attention to what cell is the active cell, because of the relative referencing. If you apply that formula as a conditional formatting formula to range E2:E481, just ensure E2 is the activecell when entering it and you'll be fine.
If you don't want to split your data via delimiter it will become much, much more complicated.