format row colour based on column A values

Question

I have about 2000 rows in an excel sheet. The first column is what I am interested in.

Example of the data is

|123|
|123|
|124|
|125|
|126|
|126|

I want to get the rows background highlighted if they numbers match. For example

123 in in two rows so these two rows should be shade1, 124 is present once so should be shade2. 125 is presnt once so should be shade1 in just one line. Line with 126 should be highllighted shade2

Ive tried to figure this out by conditional formatting but it doesnt seem to be possible. Can you help?

Thanks

Answer 1

The previous answer was obviously wrong, I misunderstood the question.

If you want to apply alternate colors to group of rows with the same value in first column:

• select all cells in the table, start the selection in A2 and move it down and right
• Conditional Formatting > New Rule
• Use a formula to determine which cells to format

• Enter the following formula:

  =MOD(SUMPRODUCT(1*($A$1:$A1<>$A$2:$A2)),2) = 1
  

• set format to shade1
• confirm

• reapeat with formula

  =MOD(SUMPRODUCT(1*($A$1:$A1<>$A$2:$A2)),2) = 0
  

• set format to shade2

This will work no matter if the first column is sorted.