# Formula to find conditional formatting

#### Pranesh

##### Board Regular
Hello All,

I have a conditional formatting applied in column C to highlight the duplicate values. Now I need a IF formula in column A to say whether column C value is duplicate or unique. Can someone help me with the formula.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### etaf

##### Well-known Member
=countif(C:C,C1)>1
would provide the TRUE or FALSE

so if you want "Duplicate" or "Unique"

= IF ( countif(C:C,C1)>1, "Duplicate" , "Unique")
and copy down

#### mole999

##### Moderator
Latest versions of excel have the ability to select by colour so you could just filter on that

#### Pranesh

##### Board Regular
=countif(C:C,C1)>1
would provide the TRUE or FALSE

so if you want "Duplicate" or "Unique"

= IF ( countif(C:C,C1)>1, "Duplicate" , "Unique")
and copy down
Hi etaf,

Thanks for your assistance. I have 2,70,000 rows of data and this is taking more than 10min to calculate this for the entire row. Is there any option to speed up this?

#### mole999

##### Moderator
any other formulas in the sheet that may also be calculating with a worksheet change event

#### Pranesh

##### Board Regular
any other formulas in the sheet that may also be calculating with a worksheet change event

There were no formulas other than that.

#### RoryA

##### MrExcel MVP, Moderator
What's the purpose of the exercise? If you sort the data on column C you can use a more efficient formula to detect duplicates, but if an item appears more than once, do you want them all flagged as duplicate, or all except the first one?

#### Pranesh

##### Board Regular
What's the purpose of the exercise? If you sort the data on column C you can use a more efficient formula to detect duplicates, but if an item appears more than once, do you want them all flagged as duplicate, or all except the first one?
Hi Rorya,

My requirement is simple. I have 2 set of data with each set have around 2,70,000 rows. 1 set is morning data and the other one is evening data. Basically I need to check how many line items were newly added in evening data and how many line item is missing in evening data while comparing to the morning data.

Initially I did a vlookup on both the sheets to find this output but it is taking nearly 20min to do it. I used single vlookup method then later came to know about double vlookup and tried that. For 1 sheet it took only 2 seconds to do vlookup but while im doing the same to the next sheet it took 15min I don't no what is the issue and im unable to fix it.

So now I thought of another idea where I have consolidated 2 sheet in a single sheet which now has around 5,40,000 rows. I have created one more column to identify morning or evening data. Now I have done a condition formatting to highlight duplicates in column C. Now one more challenge is if I try to filter column C it is taking so long time to show the drop down list as it has 5lakhs entries.

So I have thought of creating a additional column which should say me the unique and duplicates based on column C so that in that column I will have only 2 values which will be easy to filter in my drop down. So my concept is all the unique values based out of morning data are the ones which were missing in evening data. The unique ones based on evening data will be my newly added ones.

I have 2more sheets with name missing and addition. The derived output data will be pasted on those sheets.

Now even this COUNTIF formula is also taking more time. Can you let me know a best solution to speed up this activity.

#### RoryA

##### MrExcel MVP, Moderator
If you can sort the data on column C, then you can use a simple formula like:

=IF(OR(C2=C1,C2=C3),"Duplicate","Unique")

in say D2 (I'm assuming you have a header row) and copy it down.

1,095,172
Messages
5,442,821
Members
405,198
Latest member
Florence Thomas

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...