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.