# Filter Duplicates by count of a character Excel 2007

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.

-

## migrated from stackoverflow.comDec 14 '12 at 9:09

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

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

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`

-
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

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.

-