Forum Discussion

sarahahq's avatar
sarahahq
Copper Contributor
Apr 27, 2020

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 between these lists. Customers can have more than one transaction, so using the standard "highlight duplicates" conditional formatting doesn't work. 

 

This is an example of what I'm working with: Only Customer B and Customer C have discrepancies that need to be reconciled, as they don't both have an entry that isn't on the other report. 

 

Report   | Customer
Report 1 | Customer A
Report 2 | Customer A
Report 1 | Customer B
Report 1 | Customer B
Report 2 | Customer B
Report 1 | Customer C
Report 2 | Customer C
Report 1 | Customer C
Report 2 | Customer C
Report 1 | Customer C
Report 2 | Customer D
Report 1 | Customer D
Report 2 | Customer D
Report 1 | Customer D
Report 2 | Customer D
Report 1 | Customer D

 

Since only odd-occurrences (example, a customer's name appears once, 3-times, 5-times, 7-times, etc.) indicate potential discrepancies, I want to somehow use a conditional formatting rule that highlights odd occurrences without needing to make a zillion rules (I tried using the countif formula, but I'm dealing with a lot of data and there are some customers with more than a dozen occurrences, so I was hoping to find one or two formulas that would do the trick, instead of making a ton of countifs)

=COUNTIF($E$2:$E$481,E2)=1

=COUNTIF($E$2:$E$481,E2)=3

=COUNTIF($E$2:$E$481,E2)=5

=COUNTIF($E$2:$E$481,E2)=7

 

Any insight would be very helpful! Thanks! 

excel

1 Reply

  • Zack Barresse's avatar
    Zack Barresse
    Iron Contributor
    Apr 28, 2020
    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.

Resources