VBA to remove duplicates starting from the top

[GreenTee]

Hello VBA Community,

I'm trying to figure out the best way to remove duplicates. I have a sheet that gets rows added from the bottom and column A will have duplicate values. What I'm trying to do is remove the older duplicates that are in higher rows.

Unfortunately this code removes duplicates from the bottom. Any idea how I can manipulate it to remove rows that are above the duplicated value? Any help will be greatly appreciated.

Please Login or Register  to view this content.

Thanks.

[TMS]

I think you'd need to do it with a loop, starting from the bottom. You could use COUNTIF to check if there is more than one entry for the current key field and, if there is, delete the current record.

If you want/need specific advice, please post a sample workbook with some typical data.

Regards, TMS

[GreenTee]

Thanks for the info TMS.

I've attached a sample data set. So in Column A will be the ID. What I want to do is remove the rows that are above the last Duplicated ID. I already have a code in a separate workbook that adds "New" information at the bottom of the table while column A might be the same... the rest of the columns will be different. Since I know that all additional information gets added at the bottom then it's safe to assume to just delete all duplicate ID's above it.

DuplicateEntriesRemoval.xlsm

[TMS]

Try this in cell G2:

Please Login or Register  to view this content.

The fact that it is a Structured Table will automatically copy the formula down. Filter on column G for entries with "delete", select them and delete the rows.

Manual, no VBA, but very straightforward

[GreenTee]

Thanks, but I would still have to automate this. =( lol. It would be possible to do formula and then run VBA to sort for those that have "Delete" and then remove the rows... but I wanted to shy away from this for two reasons. 1. It's manual"ish" and 2. I will have rows and rows compounded over time and formulas will start to bog down the workbook.

[jaslake]

Hi GreenTee

Try this

Please Login or Register  to view this content.

[GreenTee]

Lifesaver!!

Thanks Jaslake!!

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[GreenTee]

Done. (I always seem to forget this part.)

[jaslake]

Hi GreenTee

I know...I know...tis easy to do

Done. (I always seem to forget this part.)

Have done it many times myself...but, it's important...and I remind myself...and others.

Thank you.

[GreenTee]

I actually realized that the sample workbook I added was not an accurate example of what I was looking for. I misrepresented the data. I've updated workbook to show a better example of the data set I'm working with. So really, the duplicated data isn't right on top of each other. The original data will be several rows above the most recent data. What would be the best way to account for this?

DuplicateEntriesRemoval.xlsm

[Chriz]

Hi GreenTee,

I was about to say the code that has been provided only looks one row up for the duplication.

I think you'll need to firstly, add a position column starting 1,2,3 to the right of the data. Sort Column A (assuming that's where the duplicates lie) a-z, so that duplicates are grouped together, then sort on the position column lowest to highest, then run the above code, and then maybe a final sort by position column lowest to highest just to be on the safe side.

Good news is that can all be done by code, I don't have time right now but it's definitely do able, as I do something similar at work.

Thanks

Chriz

[GreenTee]

Hmmm...I feel like that would be a long code. Wouldn't nesting a loop from the code Jaslake provided be better? (I'm not good with loops so I'm not sure.)

Also, if I went that route wouldnt sorting column A be enough to run the above code? I don't know how sorting works behind the scene but if I sorted from A-Z does it still keep the most recent inputted data at the bottom of the duplicates?

[jaslake]

Hi GreenTee

Yes it does...try it...need further help let me know.

if I sorted from A-Z does it still keep the most recent inputted data at the bottom of the duplicates?

[TMS]

This works, but not with a Structured Table. Excel manages and protects the Structured Table range.

Please Login or Register  to view this content.

Regards, TMS