Formula for cell background color

[ChrisTa]

Hi guys,

Yesterday I was struggling with my excel worksheet and one of you gave me a great idea (using a pivot chart).

Thing is that my knowledge is more than basic. To be quick my worksheet containts 40000 entries of different customers (each customer has a unique ID) but it could happen that the same customer is twice in the list. I used a macro to highlighted in red the unique ID's cell when there is a duplication.

But I am not able to create a pivot chart who do not consider duplicates cells. So what I was thinking, is to add a new column with a 1 or a 0 when the cell is red and then use this column in the pivot chart.....anyway

Does anyone know which formula I could use to say if the cell background is red = 0 if not = 1

Thanks a lot for your help

 

[Richard Schollar]

Hi

Much easier not to use the background - just use a similar formula to what you have in conditional formatting eg if customer IDs are in column A then use this in your adjacent column:

=COUNTIF($A2:A2,A2)

this will return a number >1 for duplicates, so you can use this column in your pivot and filter for the 1 values.

 

[ChrisTa]

Thanks a lot

I change the formula to =COUNTIF($A2:$A$2,A2) as it was not working (or it would be better to say I didn't know how to use it)

Many thanks