Formula to find conditional formatting

Pranesh

Board Regular
Joined
Jun 29, 2014
Messages
216
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.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,382
=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
Joined
Oct 23, 2004
Messages
9,878
Latest versions of excel have the ability to select by colour so you could just filter on that
 

Pranesh

Board Regular
Joined
Jun 29, 2014
Messages
216
=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
Joined
Oct 23, 2004
Messages
9,878
any other formulas in the sheet that may also be calculating with a worksheet change event
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
Joined
Jun 29, 2014
Messages
216
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,

Thanks for your assistance.

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
Joined
May 2, 2008
Messages
34,148
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Forum statistics

Threads
1,077,827
Messages
5,336,617
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top