Unique values based on Time

[Karthik23]

Hi ,

Can you please help me in writing a formulae for extracting uniques values based on time.
Example: If A comes to playground at 10: 00AM
B comes to playground at 10:00AM
A goes out and comes back at 10:05AM
B goes out and comes back at 10:15AM
Here if i extract names based on time i need result as A,B,0,B taking time gap as 10 min...
for every 10 min gap i need a unique value.

 

[HighAndWilder]

Where is this information held and where do you want the results displayed and or stored?

 

[Karthik23]

Where is this information held and where do you want the results displayed and or stored?

All in excel,
A column contains names
B column contains time information
Need(result) unique names in C column.
If still didn't got it , let me know i will try to upload an excel sheet ... currently i can't upload apologies for that

 

[HighAndWilder]

Put this formula in column C.

=IF(ROW()=2,$A2,IF($A2<>$A1,$A2,IF(MINUTE($B2-$B1)>=10,$A2,"")))

Let me know if I have not got your requirements correct.

 

[Karthik23]

Put this formula in column C.

=IF(ROW()=2,$A2,IF($A2<>$A1,$A2,IF(MINUTE($B2-$B1)>=10,$A2,"")))

Let me know if I have not got your requirements correct.

Thanks for the quick revert!!
Formula worked like gem!!
I will provide you more insight into my requirement because a name is appearing twice in result eventhough the time gap is below 10 min..
I hope the attached pic gives you more info i.e. John and David kn 6th and 7th row shouldn't be in the results as the time gap is below 10 min..

 

[Karthik23]

Thanks for the quick revert!!
Formula worked like gem!!
I will provide you more insight into my requirement because a name is appearing twice in result eventhough the time gap is below 10 min..
I hope the attached pic gives you more info i.e. John and David kn 6th and 7th row shouldn't be in the results as the time gap is below 10 min..

Can I have the formulae corrected to match my requirement please..

 

[Peter_SSs]

Does this do what you want (MS 365)

22 07 20.xlsm
	A	B	C
1	Names	Time	Result
2	John	10:00	John
3	Peter	10:00	Peter
4	David	10:05	David
5	Sam	10:05	Sam
6	John	10:05
7	David	10:09
8	Jerry	10:15	Jerry
9	Mike	10:20	Mike
10	Roy	10:20	Roy
11	John	10:30	John
Check Time

Cell Formulas
Range	Formula
C2:C11	C2	=IFNA(IF(B2-MAX(FILTER(B$1:B1,A$1:A1=A2,NA()))<TIME(0,10,0),"",A2),A2)

 

[Peter_SSs]

.. or for more versions (assuming the above results are what you want) try

22 07 20.xlsm
	A	B	C
1	Names	Time	Result
2	John	10:00	John
3	Peter	10:00	Peter
4	David	10:05	David
5	Sam	10:05	Sam
6	John	10:05
7	David	10:09
8	Jerry	10:15	Jerry
9	Mike	10:20	Mike
10	Roy	10:20	Roy
11	John	10:30	John
Check Time

Cell Formulas
Range	Formula
C2:C11	C2	=IF(B2-IFERROR(AGGREGATE(14,6,B$1:B1/(A$1:A1=A2),1),0)<TIME(0,10,0),"",A2)

 

[Karthik23]

Thanks for the help Peter, its working

.. or for more versions (assuming the above results are what you want) try

22 07 20.xlsm
	A	B	C
1	Names	Time	Result
2	John	10:00	John
3	Peter	10:00	Peter
4	David	10:05	David
5	Sam	10:05	Sam
6	John	10:05
7	David	10:09
8	Jerry	10:15	Jerry
9	Mike	10:20	Mike
10	Roy	10:20	Roy
11	John	10:30	John
Check Time

Cell Formulas
Range	Formula
C2:C11	C2	=IF(B2-IFERROR(AGGREGATE(14,6,B$1:B1/(A$1:A1=A2),1),0)<TIME(0,10,0),"",A2)

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.