excel - Remove time duplicates with a difference in seconds that require comparison?

Question

I have a puzzle that keeps me busy and my Excel knowledge is not in an advanced level, so I am a bit stuck.

Here is the deal: This is about error reporting. A lab instrument sometimes is popping up errors related to the sample that will be scanned. Each error pops up either 3 or 4 times (this is completely random). The only way to identify same error is the exact timestamps of when the errors occurred. The situation gets tricky here. As I said, the same error comes in "triplicates" or "quadruplicates" but the time stamps might differ.

For example, the same error will give the following exact times in the instrument software:

Sample error at 12:30:58

Sample error at 12:30:58

Sample error at 12:30:58

Sample error at 12:31:07

The thing to remove the same 3 errors is very easy with excel's "remove duplicates" feature, but the 4th one is exactly the same error but with different timestamp and I want to remove it as well. If it was only for 30 or 50 errors I could do it manually, but the files are big with thousands of them.

I have noticed that the max number of seconds between the same error is not higher than 15 secs.

The way I am removing the duplicates is by displaying only the hours and minutes (the example above would look like 12:30 / 12:30 / 12:30 / 12:31) and in general is sufficient.

But the exception comes when the error occurs at the ending seconds of a minute. When this is happening, my way of removing the duplicates is not accurate because in the example above the same error pops up 4 times, but in the last one the minute has changed so it will not get removed and will be registered as an extra error in my report.

The question for the exception of minute change: Is there any way to group these errors together, compare them with each other and based on these 15 second max difference, remove all duplicates (including the one with different timestamp) and only keep one? Does this involve any array or grouping approach?

Here is an example picture to give you the idea of how a report looks. The yellow cells are these "exceptions" were the minute increments. Error overview in excel

If something is not clear, please let me know.

question from:https://stackoverflow.com/questions/66050035/remove-time-duplicates-with-a-difference-in-seconds-that-require-comparison

Answer 1

The usual approach to this is to use offset ranges in an array formula. So here you would subtract a time from the time below it (the previous time, as they are in reverse chronological order) and compare it with 15 seconds:

=FILTER(B2:INDEX(B:B,COUNTA(B:B)),B2:INDEX(B:B,COUNTA(B:B))-B3:INDEX(B:B,COUNTA(B:B)+1)>TIME(0,0,15))

I have used Counta and Index to define the used range (assuming no blanks). The timestamp which is kept is the earliest one.