Delete duplicate rows by cell value

**gmouttou** · 11-21-2017, 11:16 AM

Hello,

Please can someone help.

I have a large database with information and want to delete rows with Duplicates based on cell Values but retain the latest row.

So for example:

I want to check if values in Cell A1 & C1 are duplicated in any rows and delete old rows. I will have F1 as my timestamp.

So if Rows 12, 16 have same values as in A1 & C1 and Row 12 has the last time stamp, I want to delete Row 1 & 16.

Hope this makes sense and sorry If I have not explained myself clearly.

Thanks.

**Keebellah** · 11-21-2017, 11:23 AM

Hi, how's you VBA knowledge, can you program a little?
The idea is to translate what you have explained to code and I would suggest that you use a helper column and place a 1 in each row that you want to delete and then when all the data has been checked delete those rows that have a 1 in that column, but then again, you will have to write that macro.

**gmouttou** · 11-21-2017, 11:26 AM

Thanks for your reply.

My VBA knowledge is very minimal.

**Keebellah** · 11-21-2017, 11:39 AM

Minimal as in none or some knowledge of VBA?

**gmouttou** · 11-21-2017, 11:43 AM

Sorry, none.

**Keebellah** · 11-21-2017, 11:53 AM

If you attach a file with the layout and some dummy data as you have it, i will write some code for you

**gmouttou** · 11-22-2017, 07:32 AM

Excel2.jpg

Am sorry, but for some reason I am unable to attach documents, so I have uploaded this as an image.

So I want to check if B2, G2 and I2 are the same in any other rows and delete them leaving the last updated row based on the Time Stamp.

I am hoping to delete rows 2 and 3.

Thanks for your help!

**Keebellah** · 11-22-2017, 05:57 PM

How to attach upload files

**gmouttou** · 11-23-2017, 05:44 AM

Thank You.

Attached is the excel sheet.

Thanks Again.

**Keebellah** · 11-23-2017, 05:49 AM

Okay, I'll take a look later today

**Keebellah** · 11-23-2017, 06:37 AM

Just to make sure you mention

I want to check if values in Cell A1 & C1 are duplicated in any rows and delete old rows. I will have F1 as my timestamp.

But A1 is empty (maybe for privacy) but B and C are the ones I can use too?
B is the Client ID (or something similar) and B the date
The timestamps in J (not F are the ones to check.
Correct?

**Keebellah** · 11-23-2017, 05:36 PM

You did not answer my questions but here's my solution based upon my assumptions being correct
I placed as much comments as I thought necessary to explain the strategy.
Another thing is you do NOT have to sort the table before processing it will work irrelevant to the sort order
Let me now , I added some records for this test
The result will be 4 rows

**gmouttou** · 11-24-2017, 08:50 AM

Apologies, I missed replying to your queries. Yes, yur assumptions are correct.

I get a 'Run Time Error 1004'. Application defined or object defined error when I run the script.

Thanks for your time.

**Keebellah** · 11-24-2017, 12:23 PM

Have you debugged the code, can you tell me at what line it happens, the file I attached works, just tested it.
Are you running it in this file or did you copy the code elsewhere?

**dangelor** · 11-24-2017, 12:34 PM

Possibly...

Please Login or Register  to view this content.

**Keebellah** · 11-24-2017, 04:29 PM

I'm still waiting for his answer, when and which line gives the error.

**gmouttou** · 11-27-2017, 06:24 AM

I get a Run Time error 13 on the file you have attached. When I debug, hope this is correct, by using the F8 key, I get Line 1( Sub test (2)) and lines 14,15,18,19,20,21 highlighted. Sorry, if I am doing anything wrong.

**gmouttou** · 11-27-2017, 06:28 AM

Originally Posted by dangelor

Possibly...

Please Login or Register  to view this content.

Thanks. Am getting a Run Time Error 1004, Application defined or object defined error.

**Keebellah** · 11-27-2017, 06:32 AM

I cannot help you with this file

, I hope that he answers.
My file works fine.

**Keebellah** · 11-27-2017, 06:36 AM

I took a alook at the code and my guess is that you get the error once there are no more rows to delete.
I also got the error using the file two after the first run.
So you will have to add an extra check that if there is nothing to delete the skip that part.

**Keebellah** · 11-27-2017, 06:41 AM

Try this

Please Login or Register  to view this content.

**Keebellah** · 11-27-2017, 06:48 AM

BTW, I checkd but this last macro does not correctly remove duplicate rows.
The result should be 4 row but it leaves 6 (2 sets still duplicated)
The data used:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is the resulf with my macro:

Please Login or Register  to view this content.

**dangelor** · 11-27-2017, 10:33 PM

Try this and be sure to copy it to a code module, not a sheet code module.

Please Login or Register  to view this content.

**gmouttou** · 11-28-2017, 05:44 AM

Thanks, this works.
When there are no duplicates though, it comes up with Run Time Error 1004, no cells were found and when i debug, it points to r.SpecialCells line at the bottom.

**dangelor** · 11-28-2017, 07:12 AM

Try this...

Please Login or Register  to view this content.

**Keebellah** · 11-28-2017, 10:59 AM

Similar to my suggestion in #21

Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Re: Delete duplicate rows by cell value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] UDF to delete all duplicate rows in a Column but one if cell value = myString, not working

[SOLVED] Delete rows on basis of duplicate & blank cell

[SOLVED] VBA macro, move cell contents, duplicate down if additional value, delete empty rows

[SOLVED] Delete rows based on duplicate cell, but leaving first and last duplicate.

Bookmarks

Bookmarks

Posting Permissions