Excel Loop and Delete oldest records

[EliseFreedman]

Hi There

I have a report in the following format
A B C D
Name Location Action Date

John Deary Govan - Fab Workshop risk assessment 30/07/2008
John Deary Govan - Fab Workshop e-learning 30/07/2008
John Deary Govan - Fab Workshop email 30/07/2008
Robert McLaney Govan - Fab risk assessment 21/08/2008
Robert McLaney Govan - Fab Workshop e-learning 21/08/2008
Robert McLaney Govan - Fab Workshop email 30/07/2008

The data is held in columns in excel.

I would like to be able to loop through the rows and delete all the older records for each employee so that ultimately I am just left with the top record for each employee

However, I am not sure how to do this

Can anyone help

 

[SkipVought]

Elsie,

your "table" does not line up. What goes where?

be a dear and do that for us, please.

Also why do you have an empty row in your table? This will be a constant headache.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

the top record for each employee
What about duplicate date ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EliseFreedman]

I dont actually have an empty row. That was just the way i pasted the data

Column A is the name
Column B is the Location e.g Govan Fab Workshop
Column C is the action e.g risk assessment
Column D is the Date

I only want the top row left for each employee which will indicate to us what stage of the training they are at so that we can produce stats giving this information.

 

[SkipVought]

Please paste your DATA here -- LINED up with the columns!

WHAT data goes in WHAT column???

It is NOT apparent! Maybe to YOU. Not to me.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

and also answer PHV's important question.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[rustychef]

If you want something "down & dirty" here you go. It sorts your list by name and then date (descending) to put the most recent entry of each name on top. It reads the most recent entry of each name and then deletes all other occurances. When the name changes, it moves on to the next employee. If you test it, please test it on a copy. I created some quick data and it worked on mine.

Sub DeleteOldestEntry()
Dim sName As String
Dim i As Long

'Select All Cells
Cells.Select
'Sort by Name then by Date. Date is in descending order so the most recent date is on top
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2"), _
Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
'Assuming that there is only one header row
Range("A2").Select
'Store value of active cell in variable
sName = ActiveCell.text
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.text <> ""
If ActiveCell.text = sName Then
i = ActiveCell.Row
Rows(i & ":" & i).Select
Selection.Delete Shift:=xlUp
Range("A" & i).Select
Else
sName = ActiveCell.text
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

 

[rustychef]

I should add a few additional notes. My code assumes that you have only ONE header row. That your NAME is in colum A and that the date is in column D.

Make sure that your dates are formated as dates (Format Cells). If they're stored as text, then the sort order may not actually be in order.

If names are miss spelled or have leading/trailing spaces, they'll show up as a unique name entry "John" and " John " are not the same.

The code will run until it comes across a blank cell in the NAME column.

 

[rustychef]

oops, one more note. The important question PHV brought up, about duplicate entries with same date for the same person. This code does not take that into consideration! How ever the sort order leaves the entries is the order in which the row is saved.

One thing you could do is to add a time column (or a "sequence" type column) that way if you have 2 identical dates, the "time" column would be the tie breaker

 

[xlbo]

I would sort by name and date as per rustychef but I wouldn't loop - I would progamatically add a formula along the lines of

=if(A2<>A1,"1st Record","Later Record")

copy that down your data set, then copy / paste special values, filter on your new column for "Later Record" and delete all rows in 1 fell swoop - can pretty much guarentee it will be much quicker than looping and deleting

...and as an aside on deleting in a loop, it should ALWAYS be done from the bottom up as otherwise your row index get muddled and rows which should be deleted get missed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rustychef]

xlbo:

I originally started out by setting something up like you suggest (I was going to mark older rows with an X then delete all X'd rows) but I had concerns about whether or not it was safe to modify data in other columns in case they contained data.

Even then, its additional code to find the empty column (since Im not familiar with the data layout), and inserting is not always an option if they have "hidden" data out at coulumn VI (I've had it happen before)

Your solution would need to check both the A and D columns (name & date) and will leave 2 entries if the dates are identical.

I agree with deleting from the bottom up when using an index/counter so everything doesnt get muddled, but on this solution I wasnt using an index/counter. Instead it loops using a comparison test (loop until the first occurence of an empty cell in column A). It doesnt even move forward using an index/counting, once again that is a comparison test to see if the name changed.

But aside from that you brought up a really good point of spotlighting a comon coding pitfall.