How to alternate highlighting of groups of rows based on matching and identifying criteria, in a table?

Perhaps this could work for you?:

Add a helper column G (columns A:G are a table (no table highlighting) in the pic below, but it doesn't have to be) with the header in G1 being FALSE

In G2 and copied down:

=IF(B2=B1,G1,NOT(G1))

Conditional formatting for 'This Table':

Hide column G.

It's OK with sorting:

Column E in this case:

but when filtered it's not so good:

although if you need it I think I can rustle up a formula for column G which will handle hidden rows properly.

Hi,

Possible method with online sql:

//select * from Sheet2;

cli_mark_duplicate_rows~Sheet2~Account Number;

https://answers.microsoft.com/en-us/msoffice/forum/all/error-in-highlight-duplicate-value-more/1b2b160a-3ec9-4740-8c3b-0b897924081f

I think I have a solution. You still need the helper column as above, but Excel will search that list for only the accounts in the even numbered lines in the helper column to match with the account number in Column B.

Set up the helper column formula to include all of your accounts. If they are not in Column B like mine adjust the column reference to match your data where it has $B2. Use this revised formula for the helper column. It will allow for a larger range than the existing data for the addition of new data.

=UNIQUE(FILTER(B2:B500,B2:B500<>0))

My helper formula is in H2. If you enter it somewhere else, you will need to adjust the column and/or cell reference in the $H$2# references below which refer to the entire spilled range of the helper formula to match your spreadsheet.

1. Select all of the cells that you want formatted which can be in one column or several beginning in Row 2. This row number must match the B2 references in the formula. Change the references in the formula to a different row if you start your selection in a different row.
2. Open Conditional Formatting>New Rule>Use a Formula.
3. In the Format Values line enter this formula adjusted as necessary as mentioned above.

=IF(IFNA(VLOOKUP($B2,FILTER($H$2#,ISEVEN(ROW($H$2#))),1,0),0)=$B2,TRUE,FALSE)

4. Click the Format button at the lower right and choose the formatting you want in the Format Cells dialogue. Then click OK and OK.

This will format all of the account numbers that are in an even row in the helper column and leave the account numbers that are in odd numbered rows in the helper column unformatted.

Hi Rich,

Thank you, this is definitely a good start. The "unique" formula helps filter for all the individual account numbers. It is a better helper column than what I was working with.

However, I have 229 account numbers and I would like to avoid writing formulas for all of them. Is there a streamlined way to create rules for all of them so that they alternate their highlighting? And, I am not sure there are 229 uniquely different colors...

Thanks again ~ AKC

Hi Alexander.

Here is a possible solution for you, but to have alternating groups of accounts it does depend on Sorting first by the Account and then by other data. If the data is sorted without sorting by the Account first, the matching account numbers will still share the same color even though they are not together. However, the account numbers in-between the formatted accounts would all be the same. In this case see the suggestion further down in this answer.

Here is the initial idea. Use a Helper column to get a unique list of the account numbers. I did this in Column H below with this formula.

=UNIQUE(B2:B13)

Then set up Conditional Formatting for every other account number. The formulas for these would be:

=$B2=$H$2

=$B2=$H$4

=$B2=$H$6

=$B2=$H$8

etc.

That will color every other Account Number in the unique list and leave the numbers in cells 3, 5, 7, etc. unformatted like this.

As I said above, if the data is not sorted first by the account, the account numbers that appear in cells H2, H4, H6, etc. would still be colored and each of those would match but none of the odd cells would be colored and would likely be next to each other in places like this.

If you are going to be sorting this way, I would think that it would be better to assign a different color to every account, but there may be too many to make that feasible.

Hopefully this helps to give some direction. Reply if you need something more specific.