Formula for cell background color

ChrisTa

New Member
Joined
Jul 30, 2007
Messages
6
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Jul 30, 2007
Messages
6
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
 

Forum statistics

Threads
1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top