Compare dates and times and delete rows

Hi all

I have a spreadsheet that has 2 pieces of data - one of a actual date and time and the other latest date and time. I need to compare each record's time and keep the record that is closest between actual date and time and latest date and time. If the two match, then I need to keep the record that has the lowest value in Col G. I have attached a sample which hopefully explains.

Is there a way I can achieve this in VBA?

Thanks

I should have explained further:

First I need to compare the time in Col E to the time in Col F for each record (each name in Col F is it's own record) and then find the times that are the closest match. I then need to keep that record and delete the others.

Second, if the closest time match has more than one record (as shown in sample), I need to keep the record that has the lowest value in Col G.

I am experimenting with comparing dates but I am having trouble with including records also.

Can anyone please assist with this?

If anyone can please help me out, it would be really appreciated

ok I should have explained further.

It is the time in Col E to compare to the time in Col H (not F).

In rows 2 and 3 in the sample, the time in Col E is the closest match to the time in Col H for all records for item 1 (AT) which has 5 records (rows 2-6). So in this case I need to keep the row that has the lowest value in Col G (which is row 2).

For the next record (item 2 - PN), the closest match in Col E to Col H is row 10 (it is an exact match), so I need to keep this row and delete the others.

For the next record (item 3 - NZ), the closest match in Col E to Col H is row 14 (it is only 1 second difference), so I need to keep this row and delete the others.

I hope this makes sense?

yes that is right. I have amended the format of the column of the columns now to be d/mm/yyyy h:mm:ss which shows the seconds difference (if any).

Does anyone know of a way I can solve this problem please?

this is perfect Arkadi, thanks!

The only thing is, I have over 150,000 rows in my sheet and I need to run this frequently. Unfortunately it took over 15 mins

Is there a way to speed it up?

Also, some cells in Col E will be blank. In this case, I need to keep the row that has the lowest value in Col G. Can your code be adjusted to reflect this?

I wonder if it is possible to speed this macro up to account for my large worksheet??

Hi !

Yes like in that thread 'cause using SpecialCells and Delete is slow …

great! Thank you so much Arkadi

Could anyone please assist me with this problem?

thank you Arkadi I ran it, it took 427 secs which is better than it was.

Also, some cells in Col E will be blank. In this case I need to just keep the row (I had to delete them in order for your code to run). Is it possible to amend the code to account for this?

Thanks

I am hoping there is someone who can assist me with this problem?

Check this

Please Login or Register  to view this content.

thank you jindon, that works great

However, could the data appear on the same sheet (sheet 1)? At the moment it adds another sheet but I don't need this.

Thanks

also, Col D sometimes has a value labelled "TBC". If this is the case, I need to keep all of these rows (and run the macro above on all the others). Is there a way the macro can be adjusted to achieve this?

Thanks again.

Change to

Please Login or Register  to view this content.

thank you jindon. Could this code be adjusted to account for the values labelled "TBC" as outlined in my previous post?

I didn't see that post...

Please Login or Register  to view this content.

thank you jindon, that is great

It is almost there. For those rows that have a value of "TBC" in Col D, I need to keep the row that has the largest value in Col H only. E.g. the values could be as follows:

25/05/2016 3:45:02
25/05/2016 3:45:12
25/05/2016 3:45:01
25/05/2016 3:45:07

In this case I only need to keep the row with the 3:45:12 value as it is the largest.

Can the code be adjusted to reflect this?

Thanks again.

Change to

Please Login or Register  to view this content.

Thanks jindon, but it appears to be deleting all of the rows now with "TBC" in them?

It leaves one line which have "TBC" in Col.D.

yes it has left one line in total, but it should leave one line for each record that has "TBC" in the value?

here is a sample so that you can see what is happening. It should delete each of the "TBC" rows but it only leaves one or two??

what I meant to say is that it should delete each record that isn't the largest value in Col H, so I have sorted the workbook and you will see for the first record, it should delete a number of rows and leave one, but it doesn't appear to be doing this?

Originally Posted by maym

thank you jindon, that is great

It is almost there. For those rows that have a value of "TBC" in Col D, I need to keep the row that has the largest value in Col H only. E.g. the values could be as follows:

25/05/2016 3:45:02
25/05/2016 3:45:12
25/05/2016 3:45:01
25/05/2016 3:45:07

In this case I only need to keep the row with the 3:45:12 value as it is the largest.

Can the code be adjusted to reflect this?

Thanks again.

Can you explain the logic completely?
This means when col.D is "TBC", it deletes all the rows but the largest in col.H?

yes that is correct. It will delete all the rows for each record, except for the largest in Col H. So in the attached sample, 1. Abo is one record. It should delete all the values except for row 7 (so rows 2 -6 and rows 8-24 to be deleted). 1. Bea is another record, so it should delete all the values except for row 27 (so rows 25-26 and row 28 to be deleted) and so on. I hope this makes sense?

OK, I think the reference was wrong.

Please Login or Register  to view this content.

I ran it on the sample sheet jindon and it deleted all but one record. So unfortunately it still is deleting too many records and not leaving the correct ones?

That's why I asked.

Originally Posted by indon

This means when col.D is "TBC", it deletes all the rows but the largest in col.H?

yes but if you see the sample sheet, you will see what I mean. The code deletes ALL ROWS, where it should only be deleting rows but leaving each record with the largest value. I have manually put the first few records in sheet 2 to show you what I mean.

thanks jindon, I wasn't explaining it correctly then. This looks great. I will check further tomorrow when I have some more time

Just inform you that, I forgot to delete the msgbox lines for my testing use.

If you have trouble, Hit Ctrl + Break to get into debug mode.

Code has been edited.

thank you jindon, that works perfectly

I almost have it, there is just one other piece that I am trying to solve for. This is a little different so I am not sure if I can post within this thread or if I need to start another.

When the value is "TBC" in Col D, I need to check to see if the value in Col G is repeated, and if it is, I need to copy the value in Col D to that row. I have inputted these into a new Col (E) in the sample sheet to show you what I mean. This would be repeated all the way down, but I'm not sure how I could go about achieving this?

I should also add that in the sample sheet, the data appears in the very next row, but this may not always be the case with the real data.

Like this?

Please Login or Register  to view this content.

yes! That is great jindon, thank you!

If I wanted to put the value in Col E (rather than replace the value in Col D) how can I adjust the code to do this?

Change 2 lines

Please Login or Register  to view this content.

perfect! Thank you so much!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

I am trying to do the same thing on Col A but it is not working. I am using the following:

Please Login or Register  to view this content.

Can you please assist me with this?

Some rows are blank, and I am trying to put the value in Col A into them.

Originally Posted by jindon

That means not ALL. Delete only non highest in same "se" col.F....
Test this hard.

Please Login or Register  to view this content.

sorry to bring up an old thread, but I have an added complexity to solve for now with this.

If there is no actual value in col E, i.e. column is blank, how can I amend the macro above to account for this? Everything works fine and has done so but now the report I receive can often leave this column blank. When it does, the macro completely removes all the data.

Don't understand what you are trying to do from you code.

Need to see your workbook with before/after.

ok I will explain further. In the attached workbook, I have shown what it looks like with example 1 before and after. This is how it currently works and it works fine. It compares times in Col E and ColF then takes the smallest value in Col G for the series.

In example 2, I don't have times in Col E, so I have shown what it should look like in Example 2 After sheet.

However the macro (when there is no data in Col E) gives me the 'type mismatch' error on this line:

Please Login or Register  to view this content.

So what is the logic to find the data to leave with col.E has no data?

I think I'm confused.

Is this related to your original question?
I mean should all the other conditions be taken in account?

yes, it is related as this was the original question (to compare the dates and times and keep only the rows that had the latest values) and the macro you provided me does this:

Please Login or Register  to view this content.

So now, keeping everything the same, if there is no values in Col E, I need to check for the latest value in Col H and keep these records. If the same date and time exists across more than one record, I need to keep the lowest value in Col G.

I believe it is to do with these lines:

Please Login or Register  to view this content.

As there is nothing in Col E, I am trying to amend the macro to say that there is no data in Col E so proceed with the rest but I just can't seem to get it?

I think I understand the logic, but the result is different.

Please Login or Register  to view this content.

thank you jindon. When you say the result is different, do you mean to the original solution you provided?

No, different from your after sheet, I mean.

yours is actually capturing the correct data. In my after sheet. I did not have the latest values for some

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Hi

I now get 'type mismatch' error on this line

Please Login or Register  to view this content.

No error and if you are testing with other data, no idea.

see attached sample taken from my 'real' data. The type mismatch error occurs here.

Could you please take a look for me?

The only thing I can think of is the date and time (perhaps because it is shown in a different format)?

Col.H is just a text not date.

In this sample you mean? Or the other sample that I provided?

Is there a way I can account for both formats in your code?

If it is as date, should have no error.

Col H in your latest file is not a date.

ok so if Col H is not a date but text, how can I adjust the code to account for both date and text?

Why only text in this workbook?

If it is mixed, almost impossible to read if it is a real date or not.

dd/mm/yyyy or mm/dd/yyyy for data like 2/3/2017, 5/4/2017 etc.

also the macro outputs to a new sheet - is there a way it can overwrite the current data in sheet 1?

replace "test" sub with

Please Login or Register  to view this content.

But what is the meaning of this, when the code doesn't work?

I get an error every time on this line:

flg1 = w(1) < dif + ((w(1) = dif) * (w(2) > a(i, 7)))

As I said, if Col.H is not date, no way, so please stop this.

sorry, wasn't clear enough. This is with date in Col H.

Please see attached and you will see the error.

I have just seen some of the dates are text. Sorry, will keep looking into it.