Need you help with formula

Hi, guys i need your help with conditional formatting, how to highlight different category in the table.
the same way you highlight every other row: mod(row(),2)=0 only this time i need to highlight every other category. Please look at the attachment
picture.jpg
rfedorovAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Without a helper column

Use this conditional formatting formula

=MOD(SUM(IF(FREQUENCY(MATCH($A$1:A2,$A$1:A2,0),MATCH($A$1:A2,$A$1:A2,0))>0,1,0)),2)
FIle.xlsx
0
 
NBVCCommented:
In K2 enter a helper formula:

=MOD(K1+(H2<>H1),2)

copied down

Then select the rows and use a conditional format like:

=K2=1 to highight every other category.
0
 
Ryan SmithSr. Systems EngineerCommented:
not sure how to get the conditional but if you fill the 2nd row with your color.

then highlight the 1 and 2 cells.

hit the paint brush

then hit cell 3 and drag it down.  It will format
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
rfedorovAuthor Commented:
Thank you both for you fast respond, I know how to manually color the rows,
To: NBVC,
in reality my category list in column A, I will make a  helper formula in column B, could you rewrite your formula please
0
 
rfedorovAuthor Commented:
I have 10000 rows and i can not manually color the rows
0
 
NBVCCommented:
Ok..

=MOD(B1+(A2<>A1),2)

Then select the rows and use a conditional format like:

=B2=1 to highight every other category.
0
 
rfedorovAuthor Commented:
Nope, i can not figure it out... I have a file
FIle.xlsx
0
 
NBVCConnect With a Mentor Commented:
Here you go

I did forget to add the $ to the conditional formula

s/b =$B2=1
FIle.xlsx
0
 
NBVCCommented:
.... which is far more process intensive.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.