Formula to find conditional formatting

[Pranesh]

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]

=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]

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

 

[Pranesh]

=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]

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

 

[Pranesh]

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

There were no formulas other than that.

 

[RoryA]

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]

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]

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.

 

[mole999]

if you build a custom format for morning, evening then you can sort by the custom rather then E, M which would be the standard > https://www.mrexcel.com/excel-tips/excel-2019-sort-east-central-and-west-using-a-custom-list/