Solved

Excel Conditional Formattign to Highlight Rows based on product

Posted on 2014-12-03
9
93 Views
Last Modified: 2014-12-04
Hello,

i need here, what formula should i use in condtional formatting that for every unique product IDs a different color is applied.

for example the table below is a sample of a big data, i have more than 38 unique product IDs and each of them have multiple information, so now i want the columns A to D have a different color for each of the unique ID
for example, all rows with Apple product in it, should be red, then all rows with Mango, should have a different color, and so on the other product,  the color does not matter what it should be, as long as it is different color from other products.

thanks.


Product      Sale      Unit g      Time
Apple      32      0.690796324      11:02:41 PM
Apple      32      0.811899831      7:10:37 PM
Apple      50      0.305000433      8:05:08 PM
Apple      2      0.859959988      7:09:10 AM
Apple      68      0.057293474      11:12:22 AM
Mango      15      0.817944042      10:44:00 AM
Mango      35      0.270569891      3:09:30 AM
Mango      52      0.668334281      3:50:55 AM
Mango      19      0.44165958      9:28:24 PM
Mango      45      0.797796571      3:59:14 AM
Mango      6      0.109264038      2:06:04 PM
Mango      92      0.922820939      2:14:53 AM
Banana      65      0.558099824      5:41:15 PM
Banana      5      0.588986268      5:34:47 PM
Banana      10      0.335050458      1:06:47 AM
Banana      75      0.528190044      9:53:05 PM
Banana      32      0.418704919      12:00:29 AM
Banana      82      0.180723069      6:33:30 AM
Cherry      16      0.311014921      2:31:11 PM
Cherry      61      0.34833418      4:43:57 AM
Cherry      92      0.431833387      11:31:47 AM
Cherry      56      0.59902096      2:18:54 PM
Cherry      80      0.118215052      2:47:41 PM
Cherry      72      0.959456081      3:35:56 PM
Cherry      1      0.499259713      10:30:42 PM
0
Comment
Question by:Flora
[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
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40478859
For each product to have a different colour you would have to have a separate rule for each product and have all rules applied to the range.

Will just an alternating colour suffice? This will change between two colours as the product changes.

Thanks
Rob H
0
 
LVL 24

Expert Comment

by:Eirman
ID: 40478907
Once you go above something like 15 colours on a monitor, it will become harder and harder
to find colours that stand out and that don't appear to merge into adjacent rows with similar colours.
There are only so many shades of green.

If you use a back/fore colour combination you could get up to perhaps 50.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40479001
I agree with the above comments, especially in regards to the number of colors applied.  With at least 38 different products, the color selection will get rather subtle and difficult to manage.

I wrote an EE article about setting up conditional "greenbar" effects in a worksheet to visually separate values in a manner like Rob described.  See if that might help resolve your issue.

Excel Conditional Formatting - The Better To See You With
(See the section titled "Ch..ch..ch..ch..Changes!")

Regards,
-Glenn
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:Flora
ID: 40479691
Thanks for your comments.

then how would I get only two color, like shading between each different product ID.  

for example Apple green and then mango yellow and then banana green again and so on
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40479729
I do this on one of my reports at work. I have column A with a formula checking column for change in value, exact formula will be posted tomorrow.  My formula puts 1 or 0 then conditional formatting based on that.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40480240
Flora...see my article.  It describes exactly how to set up a helper column to create this kind of conditional formatting.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40480526
Formula starting in A3:

=IF(D3="","",IF(INDIRECT(LEFT(ADDRESS(ROW($D$2),COLUMN($D$2),4,1),1)&ROW())=INDIRECT(LEFT(ADDRESS(ROW($D$2),COLUMN($D$2),4,1),1)&ROW()-1),INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),ABS(INDIRECT(ADDRESS(ROW()-IF(INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1))="",2,1),COLUMN(),4,1))-1)))

Value to be checked for change is in column D but allows for it to move.

Allows for blank rows between groups and new rows to be inserted within the group without messing up the calc.

Conditional formatting then just looks at column A and formats colour if value = 1 otherwise no colour applied. Gives alternating coloured banding for each group change.
0
 
LVL 6

Author Comment

by:Flora
ID: 40481996
Sorry Rob,

I could not understand how to use your sophisticated formula.

my data already starts from column A and if I put your formula in A3 then what about my products which is already there?

Glenn.

thanks. I will look into the articles you have written.
0
 
LVL 6

Author Comment

by:Flora
ID: 40482005
Hi Rob,

I used your formula in the sample as my initial sample, but does not work. it does not give any result.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question