Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

Need you help with formula

Posted on 2014-04-07
Medium Priority
221 Views
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
Question by:rfedorov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Expert Comment

ID: 39983754
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

LVL 6

Expert Comment

ID: 39983778
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

Author Comment

ID: 39983799
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

Author Comment

ID: 39983808
I have 10000 rows and i can not manually color the rows
0

LVL 23

Expert Comment

ID: 39983829
Ok..

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

Then select the rows and use a conditional format like:

=B2=1 to highight every other category.
0

Author Comment

ID: 39983864
Nope, i can not figure it out... I have a file
FIle.xlsx
0

LVL 23

Assisted Solution

NBVC earned 1000 total points
ID: 39983928
Here you go

I did forget to add the \$ to the conditional formula

s/b =\$B2=1
FIle.xlsx
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 1000 total points
ID: 39984055
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

LVL 23

Expert Comment

ID: 39984109
.... which is far more process intensive.....
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URLâ€™s and adds a header titled â€śURL Listâ€ť. It then searches through all URLâ€™s in column â€śAâ€ť, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URLâ€™s are then highligâ€¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Suggested Courses
Course of the Month9 days, 15 hours left to enroll