Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

Example:

1001/1002/1003
1001/1002/1003
1001/1002/1003

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

1001/1002/1003
1001/1002/1003

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

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

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.

=COUNTIF(A:A,A2)=(LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))+1

and if you need the opposite to detect for orange:

=COUNTIF(A:A,A2)<>(LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))+1

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
up vote 0 down vote

The formula I ended up using was

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))+1=COUNTIF($A:$A,A2),"OK","BAD")

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.