Duplicate Value - Time Saving option

[shibulal]

Hello,


I use =IF(COUNTIF($B$1:B1,B1)>1,"Duplicate","") to identify the duplicate values.

Since my worksheet does have near about 50000 line items, while I am sorting, saving,

deleting or adding columns or rows, it takes much time to execute the command.


Is there any remedy to solve this issue ?

 

[Faseeh]

Hi shibulal,


You can use

Conditional Formatting > Highlight Duplicate Values

Option because you mentioned that you are already sorting the data. Once you sorted the data for Column B and use this option you will find Duplicates just one after another wherever they occur. It will be easier for you to handle them.


What will you do once you identified them?


Regards,

Faseeh

 

[NARAYANK991]

Hi Shibu ,


You can opt to set your Recalculation to MANUAL.


Narayan

 

[shibulal]

Dear Faseeh,


The question is genuine. What I will do once I identified them ?.


I will make them unique by adding ~a,~b... via CONCATENATE. Tilt is given because in future if I want to get the genuine I can extract it thru TEXT TO COLUMN.


Oops.....


Dear Narayan, Manual means "Value PAste" ? That also consumes a lot of time (for value paste).....

 

[SirJB7]

Hi, shibulal!


I agree with NARAYANK991, you should set Calculation to Manual before sorting, adding or deleting columns (i.e. operations that involve a high quantity of cells), but not when saving, it's better that takes more time when closing than when opening.


Now, if you're doing things within VBA you must place this two pair of statements at the beginning and at the end of the related code, respectively:


Application.CalculationMode=xlCalculationManual

Application.ScreenUpdating=False


Application.CalculationMode=xlCalculationAutomatic

Application.ScreenUpdating=True


If you do it manually from the top menus, you can only work with Calculation mode from Formula tab, Calculate/Calculation group, and you'll find there the two options.


I guess that with "Value Paste" you were referring to copy & paste special operations. You should be copying and pasting normally as you do today, and each time you want to update the displayed values (recalculate), you press F9.


Regards!

 

[Hui]

Shibulal


If your going to use a formula why not kill 2 birds with 1 stone!


Use something like =IF(COUNTIF($B$1:B1,B1)>1,B1&"~"&Row(),B1)

Then copy the new Column and paste over itself with values

Delete column B