Finding duplicates within 5 seconds

[Leo55]

I am unsure if this can be done,
I have searched around and nothing quite his the mark.

What I am trying to do is write something that looks at the date and time columns and if they are within 5 seconds of each other will look at two other columns within the data and if they are the same numbers are the same will delete(or highlight) the duplicate row and leave one.


Thank you in advance

 

[Leo55]

Is this even possible via function or VBA? Even if it could just highlight the duplicates within the 5 seconds would be useful.

Thanks in advance

 

[Joe4]

I think it would be necessary to see how your data is structured.
Can you show an example of your data and your expected result (just be sure to remove any sensitive data first)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, is your data already sorted by date/time?
If not, can it be re-sorted?

 

[Leo55]

I think it would be necessary to see how your data is structured.
Can you show an example of your data and your expected result (just be sure to remove any sensitive data first)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, is your data already sorted by date/time?
If not, can it be re-sorted?

Hello, thank you for the reply,

Yes the data is sorted by date and time (one column for each).
below is some dummy data to give you an idea of the data layout.

If it is possible I would like to be able to get it to search the date and time, if there are calls occurring within 5 seconds of each other, for it to check Columns E and F, if the callers (staff and customer) are the same, then to highlight or that are within that time window a colour (don't mind colour, green or red maybe?).

thank you in advance.

Book1
	A	B	C	D	E	F
1	Date	Time	Duration	Method	Staff Number	Customer Number
2	01/01/2020	12:33:25	0	Phone	123456789	77849265
3	01/01/2020	12:33:27	0	Phone	123456789	77849265
4	03/03/2020	15:45:55	55	Phone	22486597	1892675
5	04/03/2020	09:22:20	36	Phone	1579856	48132158
6	04/03/2020	09:22:20	36	Phone	1579856	48132158
7	04/03/2020	09:22:22	36	Phone	1579856	48132158
8	05/03/2020	16:56:33	59	Phone	1854518	5126105
Sheet1

 

[Joe4]

I would use Conditional Formatting to do this.

If you select the range A1:F8, go to Conditional Formatting, select the "Use a formula to determine which cells to format" option and enter this formula:

=OR(AND($E2=$E1,$F2=$F1,ROUND(IFERROR(($A2+$B2-$A1-$B1),99),9)<=ROUND(5/86400,9)),AND($E2=$E2,$F2=$F3,ROUND(IFERROR(($A2+$B2-$A3-$B3),99),9)<=ROUND(5/86400,9)))

and then choose any highlighting color you want, it should highlight the rows where columns E and F are the same and the date/time values are within 5 seconds of each other.

So on your sample data, it would look something like this:

 

[Leo55]

I would use Conditional Formatting to do this.

If you select the range A1:F8, go to Conditional Formatting, select the "Use a formula to determine which cells to format" option and enter this formula:

=OR(AND($E2=$E1,$F2=$F1,ROUND(IFERROR(($A2+$B2-$A1-$B1),99),9)<=ROUND(5/86400,9)),AND($E2=$E2,$F2=$F3,ROUND(IFERROR(($A2+$B2-$A3-$B3),99),9)<=ROUND(5/86400,9)))

and then choose any highlighting color you want, it should highlight the rows where columns E and F are the same and the date/time values are within 5 seconds of each other.

So on your sample data, it would look something like this:
View attachment 73093

That seems to have done the trick, thank you for that!

 

[Joe4]

You are welcome!
Glad I was able to help.