Flagging formula for file names that do not occur a set number of times

**njmiller31** · 10-23-2012, 05:02 PM

Hello All,
Thanks for reading this. I have 3200 pictures that are all supposed to have 31 measurement points placed on them. When I copy the measurement data to Excel (roughly 99k rows), I need a conditional formatting formula that will highlight the cells if a file name does not occur exactly 31 times. I have attached an example of what I'm looking for, but here's the gist of it:

A B C D
File ID Point x y
Number
Apples.tif 1 147 44
Apples.tif 2 147 182
Apples.tif 3 148 245
etc. etc. etc. etc.
Apples.tif 31 55 370

Oranges.tif 1 137 45
Oranges.tif 2 160 203
Oranges.tif 3 157 265
etc. etc. etc. etc.
Oranges.tif 32 50 299 <---- All the cells containing "Oranges.tif" would be highlighted because Oranges.tif occurs 32 times.

Example 1.xlsx

THANKS AGAIN!

**Pete_UK** · 10-23-2012, 07:16 PM

Highlight all the data from A2 down to the bottom of that column, then click on Conditional Formatting | New Rule | Use a formula ..., then put this formula into the dialogue box:

=COUNTIF(A:A,A2)<>31

Then click the Format button, then the Fill tab and choose your colour. Then click OK twice to exit the CF dialogue box.

Hope this helps.

Pete

**Special-K** · 10-23-2012, 07:20 PM

Try Conditional Format

=(COUNTIF(A$2:A$125,A2)<>31)

and highlight as required
Use Format painter to copy to other cells in the list

**njmiller31** · 10-23-2012, 09:34 PM

Oh man! I can't believe I didn't think to use the COUNTIF function. That was a classic case of over-thinking things on my end.

Thanks for your help, Pete!