Solved

Excel Conditional Formattign to Highlight Rows based on product

Posted on 2014-12-03
9
88 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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 32

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 23

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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 32

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 32

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now