Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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
0
rfedorov
Asked:
rfedorov
2 Solutions
 
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 SmithCommented:
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
NBVCCommented:
Here you go

I did forget to add the $ to the conditional formula

s/b =$B2=1
FIle.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
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:
.... which is far more process intensive.....
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now