Delete duplicating entries across the worksheet

I have a 5 line macro offering which I can post after adjustments/additions are made according to msg#2

Hello Sir, I am really sorry for my lack of knowledge, I am trying to submit a link but the forum won't allow me to do so until I post a few messages. I will make sure to follow the rules for any such matter in the future. Thank you

Originally Posted by p45cal

I have a 5 line macro offering which I can post after adjustments/additions are made according to msg#2

Sir I am trying to post the link but perhaps being very new the forum won't allow me to do so.

Sir I have updated my original post with a truncated link as the forum wasn't allowing me to post the full link so I removed www part and updated it. Thank you

Sir, this line of code produced a Run-time error 1004 (No cells were found).:
.SpecialCells(xlCellTypeConstants, 1).Select

My sample data is in columns A to E but my original data file would span from columns A to AD.
Regards

Yes, it is working without any error now.

Just a couple of requests, if you could kindly make those amendments would be great:

1- It is deleting the very last cell of the range (E17 from the sample data) despite it has no duplicates.
2- The number of rows in each column would vary (column A could have fewer data cells and the other columns in the right could have more) so I hope it won't be an issue.
3- How can I amend it to cover my range from columns A to AD instead of the current range of A to E.

Regards

I am not saying Thank you as you advise not to otherwise I cannot express my gratitude for the help you have already rendered.

Originally Posted by aghaffar82

Sir this line results in a type mismatch error (Run-time error 13):
For Colm = UBound(myVals, 2) To 2 Step -1

Look back at the message, it's been updated.

This seems to have worked like a charm, I will just double check and then mark this as solution. Will update you shortly. Best Regards & Thank you so much.

Originally Posted by p45cal

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Both of these codes run correctly but take ages for just about 3000 rows and 20 columns. It took almost an hour and eventually excel crashed.

Unfortunately, won't be suitable for me as I have to run over 100k rows and if for 3000 rows it resulted in that much time and even crashed without getting the final output.
And I did not use the last line to delete the cells and move up.

Is there some other method I could try? Thank you

OK, if there are duplicates within column A, do these need to be eliminated too?

Yes, those ones also need to be eliminated. Only one unique entry is required keeping in consideration that if a duplicate entry occurs the left-most and the top-most to be kept and the rest of them should go. Thank you

Hi, aghaffar82
Try this:

Please Login or Register  to view this content.

Originally Posted by Akuini

Hi, aghaffar82
Try this:

Please Login or Register  to view this content.

This thing worked like a space rocket, absolutely marvelous.
For about 150,000 entries it took just 5 seconds. And when I ran about 30 million entries (1million x 30 columns) it cleared out in about 5 hours and I would say that's just outstanding. Thank you so much to you Sir may you be blessed. Regards

Originally Posted by p45cal

Akuini beat me to it! The only thing different I would have done is to close up the gaps, so with a tweak to Akuini's code.
I cannot post code without getting an 'access denied' firewall block because this code apparently contains html code (with or without code tags) so here's a picture of the code (ugh!)
Annotations in the code (well, picture):

Attachment 708791
6 seconds for A1:T139264.

Do NOT have any conditional formatting present for such a range!

Thank you so much Sir, you have been very kind, I will give this code a go to and then let you know how it goes. Best Regards

Originally Posted by aghaffar82

This thing worked like a space rocket, absolutely marvelous.
For about 150,000 entries it took just 5 seconds. And when I ran about 30 million entries (1million x 30 columns) it cleared out in about 5 hours and I would say that's just outstanding. Thank you so much to you Sir may you be blessed. Regards

5 Hours!!
I tested it on A1:AD1004092 (30,122,760 entries); less than 1 minute and a half.

Originally Posted by p45cal

5 Hours!!
I tested it on A1:AD1004092 (30,122,760 entries); less than 1 minute and a half.

Sir, perhaps you have a super-computer I tried again with the updated code you shared in the picture and it's been 10 mins and still working. Not sure how long it would take now but I am assuming it would take the same time as before. Thank you

And there is no conditional formatting in my file.

There's something else slowing it up; it can't be 200 times slower, mine isn't a super computer.
What Excel version?

It's Excel 365, windows 10 and nothing wrong with the computer, it's pretty fast usually with 8GB Ram (1.8 GHZ) processor. Nothing is in the background and even if other applications like chrome even closed during the time. And the test I did with 5000 rows x 30 columns, it took just 5 seconds but with the heavier file it takes way more time.

Just for your information, the file is quite huge it has a size of 165 MB and I don't have any clue why that's so huge with that simple text entries, without any formula and conditional formatting and perhaps that's the reason it's that slow. Thank you

1. Is it possible that you have data that exists outside column 1-30 that you're not aware of? so if say you have data in column 500 then the code will process 500 x 1 million cells.
Try checking the last column with this code:

Please Login or Register  to view this content.

2. How many unique values do you have? about 1 million, 10 million or 20 million?
More unique values means the dictionary is getting bigger so it will take longer to process the data.

Originally Posted by Akuini

1. Is it possible that you have data that exists outside column 1-30 that you're not aware of? so if say you have data in column 500 then the code will process 500 x 1 million cells.
Try checking the last column with this code:

Please Login or Register  to view this content.

2. How many unique values do you have? about 1 million, 10 million or 20 million?
More unique values means the dictionary is getting bigger so it will take longer to process the data.

Hello, the final Result shows a de-duplicated and unique count of 7.73 million (7,736,330) cells/entries. And the original file has about 13.27 million (13,266,493) cells/entries spanned in 30 columns and the longest column goes at row number 1,004,799. And if I hit ctrl+end the cursor moves in the same row i.e. 1,004,799 in column AD (AD1004799) and there is no data beyond the column AD. The macro also shows the last column = 30. Thank you

Hello, the final Result shows a de-duplicated and unique count of 7.73 million (7,736,330) cells/entries. And the original file has about 13.27 million (13,266,493) cells/entries spanned in 30 columns and the longest column goes at row number 1,004,799.

Is it just one time job that you've already finished or are you going to do this again?
Because it looks like you have a lot of blank cells in the data set, so I think I can make the code faster.
Let me know if you still need a faster code for this job.
And thanks for add reputation.

Originally Posted by Akuini

Is it just one time job that you've already finished or are you going to do this again?
Because it looks like you have a lot of blank cells in the data set, so I think I can make the code faster.
Let me know if you still need a faster code for this job.
And thanks for add reputation.

Thank you, dear, yeah, it was a one-time thing for now but may be required to be done in the middle of next year. If those blanks cells are the culprit, I think, first I can find and replace them with a 0 or something alike and see what happens. Thank you

Originally Posted by aghaffar82

It's Excel 365, windows 10 and nothing wrong with the computer, it's pretty fast usually with 8GB Ram (1.8 GHZ) processor. Nothing is in the background and even if other applications like chrome even closed during the time.
…snip…
Just for your information, the file is quite huge it has a size of 165 MB and I don't have any clue why that's so huge with that simple text entries, without any formula and conditional formatting and perhaps that's the reason it's that slow. Thank you

OK, could you share the file on a file-sharing site (keep it secure with a password required to gain access if it's sensitive, or if you don't want it in the public domain, and supply that password to me/us in a Private Message at this site) or Private Message me here and I'll supply an email address for direct sending.

I'm very hopeful we can make this fast for you, one way or another…