?
Solved

Excel Conditional Formattign to Highlight Rows based on product

Posted on 2014-12-03
9
Medium Priority
?
96 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

764 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