Filtering for multiple criteria that match and time difference

[Juja]

Hello to all I am new here but need some help.

I searched on this forum but didnt find somethig like i need.
Tried to do what i need with fitering and copy to new sheet then filter againg and copy to new sheet but didnt get what i need.
Need to filter put-aways between 150 peoples to get shorter table that need to be checked.

Need to filter for 5 different criteria to match and 6 would need to be that time beetwen 2 same is less than 60sec.

Something like this:

C1	C2	C3	C4	C5	C6
Value1	Value2	Value3	Value4	Value5	V6
1	A	A1	B1	C1	23.1.2020 13:40:35
2	B	A2	B2	C2	23.1.2020 12:30:00
3	C	A3	B3	C3	23.1.2020 13:40:00
1	A	A1	B1	C1	23.1.2020 13:41:20
2	B	A2	B2	C2	23.1.2020 12:35:00
3	C	A3	B3	C3	23.1.2020 13:40:00
1	A	A1	B1	C1	23.1.2020 13:43:00
2	B	A2	B2	C2	23.1.2020 12:35:50
3	C	A3	B3	C3	23.1.2020 13:40:00
1	C	A3	B3	C2	23.1.2020 16:23:00
3	B	A1	B1	C2	24.1.2020 8:11:11
2	D	A1	B3	C3	22.1.2020 11:55:35

And result is this:
New table with only rows where first 5 values are same and 6 value is inside 60 sec difference

1	A	A1	B1	C1	23.1.2020 13:40:35
1	A	A1	B1	C1	23.1.2020 13:41:20
2	B	A2	B2	C2	23.1.2020 12:35:00
2	B	A2	B2	C2	23.1.2020 12:35:50
3	C	A3	B3	C3	23.1.2020 13:40:00
3	C	A3	B3	C3	23.1.2020 13:40:00
3	C	A3	B3	C3	23.1.2020 13:40:00

Don't know if that is possible to create.

Thank you all

 

[jasonb75]

As long as your dates are valid you could do it by adding an extra column to check for qualifying rows, then filter that to get your results. If your dates are text strings that look like dates, then this will not work.

Using the method below, you could filter column G to show only results greater than 1, then sort as needed.

Book1
	A	B	C	D	E	F	G
1	Value1	Value2	Value3	Value4	Value5	V6	Results
2	1	A	A1	B1	C1	23/01/2020 13:40:35	2
3	2	B	A2	B2	C2	23/01/2020 12:30:00	1
4	3	C	A3	B3	C3	23/01/2020 13:40:00	3
5	1	A	A1	B1	C1	23/01/2020 13:41:20	2
6	2	B	A2	B2	C2	23/01/2020 12:35:00	2
7	3	C	A3	B3	C3	23/01/2020 13:40:00	3
8	1	A	A1	B1	C1	23/01/2020 13:43:00	1
9	2	B	A2	B2	C2	23/01/2020 12:35:50	2
10	3	C	A3	B3	C3	23/01/2020 13:40:00	3
11	1	C	A3	B3	C2	23/01/2020 16:23:00	1
12	3	B	A1	B1	C2	24/01/2020 08:11:11	1
13	2	D	A1	B3	C3	22/01/2020 11:55:35	1
Sheet13

Cell Formulas
Range	Formula
G2:G13	G2	=COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,B2,$C$2:$C$13,C2,$D$2:$D$13,D2,$E$2:$E$13,E2,$F$2:$F$13,">="&(F2-"00:01:00"),$F$2:$F$13,"<="&(F2+"00:01:00"))

 

[Juja]

Hello,

thank you for that, but I can't get it to work.
Meanwhile I tried something to make filtering simple.
Created one more column A1&B1&C1&D1&E1 and filter duplicated value to get what I need.
Only thing is that filtering by time that can't get it to work

 

[jasonb75]

Are you sure that your dates and times are in a valid format?

If the data has been copied from an external source then it is highly possible that they are text strings that look like dates.

Click on one of the date and time cells, then press Shift Ctrl and ! together, if the date changes to a decimal number then it is valid (press ctrl z to change it back), if it doesn't change then it is text, in which case then they will need to be converted to make them useful.
The easiest way to do this is to use find and replace (ctrl h) on the date and time column to replace . with /

Once done the formula method that I suggested earlier will work.

 

[Juja]

I tried manualy enter value in date and time column, but still get some error.
Also replaced , with ; to get correct filter range and values in formula.

But this can't get this time filter to work.
I try removed sec and to filter only mins but nothing.

 

[Juja]

Ok, managed to make something but now again is problem with date value.

 

[jasonb75]

To avoid any translation errors, can you copy a few rows to a blank workbook, then upload it to dropbox or similar?

If you do that I can test the formula with the actaul data format to find the cause of the problem.