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.
 

Some videos you may like

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
Joined
Oct 24, 2012
Messages
3,777
Office Version
365
Platform
MacOS
=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
10,274
Office Version
2019, 2016, 2013
Platform
Windows
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
10,274
Office Version
2019, 2016, 2013
Platform
Windows
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,793
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,793
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top