# Google Sheets Alternate Row Color by Unique Sorted Values

In Google Sheets there is now the Alternating rows formatting. It's almost the equivalent of of tables in Excel.

Background

What if I have a sheet that is sorted by Invoice #'s, my unique number associated with a bill I send out to someone, but that someone breaks my invoices up into multiple line items associated with their own number called a Purchase-Order-Number.

Question

How do I Highlight every other invoice such that I can more easily distinguish which Purchase-Orders belong to which Invoices?

Additional Applications Any sheet sorted by a particular column whose values are not unique. For instance, if you have sales agents, and you want to highlight every other agent.

Scenario

If you have 500 items, in column B, but only 20 of them are unique invoices due to them being broken up into purchase orders. It makes sense to keep them separate so you can more easily reconcile checks that are written to you.

Solution

The solution I came up with involves using the custom-formula for conditional formatting.

If column A is the Purchase-Order Numbers ( that are entirely unique ) and column B are your invoices that repeat multiple times. Assuming you have headers in row 1. `=ISODD(MATCH(\$B2,UNIQUE(\$B\$2:\$B),0))`

Explanation of Solution

`UNIQUE()` is an array returning formula that is exclusive to google sheets (it does not work in excel). This allows you to figure out how many unique invoices exist within column B. So `UNIQUE(\$B\$2:\$B)` returns a list of only the unique numbers, removing all duplicates.

`MATCH()` returns an integer of where an item is located in a 1 dimensional range or array. I used it to look at each item in column B and find it's location within the array created by `UNIQUE(\$B\$2:\$B)` ; SO `MATCH(\$B2,UNIQUE(\$B\$2:\$B),0)` will look at cell `B2`, and find where it is located in `UNIQUE(\$B\$2:\$B)` and return a number that represents which place it is in the list.

`=ISODD()` returns `TRUE` or `FALSE` depending on the number you give it. For Instance `ISODD(1)` will return `TRUE`. So if we wrap `ISODD()` around our match-unique formula it will alternate with `TRUE`, `FALSE` per each different invoice. SO, `=ISODD(MATCH(\$B2,UNIQUE(\$B\$2:\$B),0))`

• Ahh, I see. Accepted. I bet I can improve this answer, I'll have to revisit it in the future. Commented Aug 18, 2017 at 16:00
• Just add this little change to the previous answer : `=ISODD(MATCH(\$B2,UNIQUE(\$B\$2:\$B),0))` to have exact matching.
– Kiv
Commented Mar 18, 2018 at 19:40
• As a comment by Kiv points out, `match` should be given third argument 0 in order to work as expected here.
– user135384
Commented Mar 18, 2018 at 21:14