Take the 2-minute tour ×

I have a data list of around 32000 items in column A. Each of these items has many duplicates, some having 1 duplicate, up to about 5 or so max. Each cell has multiple sku's, divided by a /.

What I need to do is determine the count of /'s in a single cell, and if that count equals the amount of duplicates, color the cell green, if not, color the cell orange.



All 3 of these cells would turn green, there are 2 slashes, and 2 duplicates of each cell (or 3 total cells duplicated)


Both of these would turn orange, as there are 2 slashes, and 3 entries. They would also be orange if there were 4 entries, or any number other than 3.

I can use formulas as necessary, and can even do this in multiple steps, such as an extra calculation cell that outputs yes or no, and I can color them myself by filtering etc.

I prefer to avoid VB in this as it is used by many users that have absolutely no clue how to use excel, but if necessary, I can use VB.

share|improve this question
Can you clarify why three green and two orange when all 5 are the same? –  pnuts Dec 13 '12 at 19:31
Also "there are 2 slashes, and 3 entries"? –  pnuts Dec 13 '12 at 19:42
@pnuts the 3 are green as there are 2 /'s for 3 entries, meaning a total of 3 part numbers, and 3 entries. The bottom example is orange as there are 3 part numbers, and 2 entries. –  NRGdallas Dec 13 '12 at 20:19
Thanks. I'm not much the wiser BUT you have and answer I see. –  pnuts Dec 13 '12 at 21:01
add comment

migrated from stackoverflow.com Dec 14 '12 at 9:09

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

I believe this can be done using Conditional Formatting. As the two colors are exact opposites, you can either color all the cells 1 colors and detect (and change color) for other condition, or you can use 2 conditional format rules.

Anyway, formula to detect which cells should be colored green. Assumes that data is in col A and the active cell is A2.


and if you need the opposite to detect for orange:


share|improve this answer
im not entirely sure I understand how this works - I need to compare the amount of duplicates to the amount of /'s in the item. There may be both more or less items divided by slashes as there are entries. From what I can tell, this formula does nothing to determine how many duplicates there are. –  NRGdallas Dec 13 '12 at 20:26
marking yours as correct as after further review, its basically the exact same as what I ended up using :), just in reverse! –  NRGdallas Dec 13 '12 at 20:44
You may have figured this out, but basically the problem is to figure out how many "/" are in the cell. If we take the length of text - the length of text after we remove the "/", we can know how many there were. I add 1 since we want item count, not slash count. Then we compare that with the COUNTIF result to figure out if there's a match. –  Luke M Dec 13 '12 at 21:05
add comment

The formula I ended up using was


Placed into Cell B2 - then filled down - I then filtered column B by OK or BAD, and just manually colored the bulk column A.

share|improve this answer
add comment

Your Answer


