Solved

Excel Conditional Formattign to Highlight Rows based on product

Posted on 2014-12-03
9
85 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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Author Comment

by:Flora
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
Flora...see my article.  It describes exactly how to set up a helper column to create this kind of conditional formatting.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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 5

Author Comment

by:Flora
Comment Utility
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 5

Author Comment

by:Flora
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

9 Experts available now in Live!

Get 1:1 Help Now