Solved. Excel 2000 - delete duplicate records?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Hi.
I am using an Excel worksheet with several thousand records listed. On a daily basis, I get an updated list, but I need to keep hold of some of the 'old' records.
At present I'm copying the new list, adding it to the end of my existing list and then filtering it so that only unique records are visible.
Obviously, this means the list is ever-increasing with many duplicated records (hidden or otherwise).
Is there a way to simply delete the duplicate records (so I can just keep one record of each) rather than hide them?

Ta
Gram
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Cheers Dreamboat.
I think I need to use the 2nd part of the DeleteDuplicateRows macro (beginning "Here's another procedure that may be useful").
I was previously only storing (3) selected columns, but I could also store an additional available column - either "expiry date" or "days remaining", and then I'd be able to use one or other DeleteDuplicateRows macro.

Thanks!
Gram
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Gram starts talking about the code....LOL. Do you actually think I looked? Not on your life. I figured you are code-literate enough, and I just happen to know where that dern file was.

Like that, Randy? One of my FAVES!
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
I figured you are code-literate enough
LOL!
I'm afraid I'm quite code-illiterate, I'm just starting to learn bits now. I guess I was really talking to myself!
Anyway, it worked and I even managed to customise it a little, so I'm happy.

I didn't think you'd have just read it, Dreamboat - I thought you'd have memerised it!! :D

Gram
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
I have found that if I insert new version of an existing record above the earlier version, it successfully deletes the older version.

However, if I enter the new version of an existing record below the earlier version, it deletes the new version!

The problem here is the progressive additional fields, that are not taken into account in the deletion:
1st, I have the "Term" in Column A - which is either "Early Termination" or "Expiry". However, records sometimes start off as Early Termination, but later become Expiry.
2nd, I have the "Colour" in Column C. Initially, the colour is often blank, until it is positively identified later.

This means that if I add the new records to the bottom of my master list and then run the code, all of the 'updates' to Term and Colour will be lost, because it effectively reverts back to the original version of each record.

Can anyone help me correct the code, which currently looks like this:

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("B1").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "B").Value = Cells(RowNdx - 1, "B").Value Then
If Cells(RowNdx, "A").Value <= Cells(RowNdx - 1, "A").Value Then
Rows(RowNdx).Delete
Else
Rows(RowNdx - 1).Delete
End If
End If
Next RowNdx
End Sub

Thanks
Gram
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Anyone got any ideas on this yet?

I can bypass the problem by just pasting new info and moving existing cells down before running the code, so if no one can help, I'll live with it as it is.

Gram
 
Joined
Oct 13, 2000
Messages
941
Hi Gram, I'm not an Excel code freak, and some of what you've got there looks pretty strange to me, but I can tell you that you're stepping "backwards" through your rows, and therefore always deleting the first version found I'm guessing. What makes you step backwards is this line:

For RowNdx = Range("B1").End(xlDown).Row To 2 Step -1

Let's read it together, shall we? This says, for every row from the last filled cell in column B up to the second row, walking backwards, i.e. if B1000 has the last data, it'll go B1000, B999, B998, etc. To make it go forward, turn it around and drop the Step bit:

For RowNdx = 2 To Range("B1").End(xlDown).Row

This should do it. Although your data will have to come at the end, and there may be some other reason unknown to me why the programmer made it walk backwards...
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Down - I followed your code, but it doesn't work quite right.
If I add a new version of the last record in the list, it correctly deletes the original. Good.
However, if the record to be replaced is not the last record, it does nothing.

So,
If I have a list like this:
Early . . . . . ABC123 . . . . . Red
Early . . . . . DDD456 . . . . . Green
Early . . . . . GGG999 . . . . . Pink

and I add this record to the bottom:
Full . . . . . GGG999 . . . . . Pink

then it successfully deletes the original of GGG999 (marked "Early") and keeps the new version, (marked 'Full")

However, if I add this record:
Full . . . . . ABC123 . . . . . Bright Red

it keeps both records for ABC123.

Gram
 
Joined
Oct 13, 2000
Messages
941
Yep. Not a problem there, it's just not a very good procedure :). Because--look at the RowNdex - 1 bit--it's only comparing a dupe record to the one immediately preceding it. You'll have to run a sort on your data first, in other words, to use it with this version. Or write something a lot more sophisticated...
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
In that case, Down, I was better off with the code as it was in the first palce, and just adding the info to the top of the list (moving subsequent cells down), wasn't I???

Gram
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Okay, I added the code to put the relevant field in ascending order first, plus some re-formatting, and I'm happy once more.

Thanks for your help guys. We can call this one solved.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top