Unique values based on Time

Karthik23

New Member
Joined
Jul 15, 2022
Messages
15
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi again,
Now my requirement got abit extended, can you please help with the formulae.
Requirement in single line: Need unique count for every past 10 min, hope attached excel gives you more clarification.
As per attached excel Need formulae for unique count in G column.
 

Attachments

  • IMG_20220722_221000 (1) (1).jpg
    IMG_20220722_221000 (1) (1).jpg
    233.8 KB · Views: 4
Upvote 0
See if this does what you want. If you want helpers to test with your given sample data then you need to provide it in a form that we can copy from (eg XL2BB) - not a picture.

See if this is what you mean.

Karthik23.xlsm
ABC
1NamesTimeResult
2John10:001
3Peter10:002
4David10:053
5Sam10:054
6John10:054
7David10:094
8Jerry10:155
9Mike10:206
10Roy10:207
11John10:308
Check Time (2)
Cell Formulas
RangeFormula
C2:C11C2=N(C1)+(B2-IFERROR(AGGREGATE(14,6,B$1:B1/(A$1:A1=A2),1),0)>=TIME(0,10,0))
 
Upvote 0
Hi Again,

Hope you are doing good!!

As I can't download the XL2BB due to restrictions, I am pasting the data below.

The requirement has been modified now i.e. every user time should calculate the past 10 min and need to return the unique value. hope the below data gives clear picture.
Names​
Date​
Time​
Unique Count​
Description
John​
7/1/2022​
10:00:00 AM​
1​
Here John is logging first so count is 1​
John​
7/1/2022​
10:03:00 AM​
1​
Here John is again logging(within 10 min) so count will stay as 1​
Peter​
7/1/2022​
10:05:00 AM​
2​
Here Peter is logging at 10:05 the formulae should check before 10 min i.e. 09:55 then the unique should reflect in this case it is 2​
David​
7/1/2022​
10:05:00 AM​
3​
Here David is logging at 10:05 the formulae should check before 10 min i.e. 09:55 then the unique should reflect in this case it is 3​
Sam​
7/1/2022​
10:05:00 AM​
4​
Here Sam is logging at 10:05 the formulae should check before 10 min i.e. 09:55 then the unique should reflect in this case it is 4​
John​
7/1/2022​
10:09:00 AM​
4​
Here John is logging at 10:09 the formulae should check before 10 min i.e. 09:59 then the unique should reflect in this case it is 4 because john already logged in at 10:03 which is within 10 min
David​
7/1/2022​
11:00:00 AM​
1​
Here David is logging at 11:00 the formulae should check before 10 min i.e. 10:50 then the unique should reflect in this case it is 1
Jerry​
7/1/2022​
11:07:00 AM​
2​
Here Jerry is logging at 11:07 the formulae should check before 10 min i.e. 10:57 then the unique should reflect in this case it is 2​
Mike​
7/1/2022​
11:18:00 AM​
1​
Here Mike is logging at 11:18 the formulae should check before 10 min i.e. 11:08 then the unique should reflect in this case it is 1​
Sam7/1/2022
11:59:00 PM​
1​
Warner7/2/2022
12:05:00 AM​
2​
Even the date changes the unique count should calculate the before 10 min, here in this case it is 2​
 
Upvote 0
The requirement has been modified
This is the third requirement set. It would be good to know if this is going to be the final requirement(?) so that time is not spent developing something that is very quickly obsolete. ;)

Would a formula for Excel 365/2021 only be acceptable?
 
Upvote 0
Really sorry for the modifications this will be the final set :) Thanks alot for your efforts on this.
I am not clear with the second question Peter. Need a formulae like old formulae's which we can execute in Excel
 
Upvote 0
I am not clear with the second question Peter.
The second question was whether you will accept a formula like this that will only work in the versions I mentioned.
If not, then I will leave it to somebody else to battle with a much bigger formula.

Karthik23.xlsm
ABCD
1NamesDateTimeUnique Count
2John1/07/202210:00:00 AM1
3John1/07/202210:03:00 AM1
4Peter1/07/202210:05:00 AM2
5David1/07/202210:05:00 AM3
6Sam1/07/202210:05:00 AM4
7John1/07/202210:09:00 AM4
8David1/07/202211:00:00 AM1
9Jerry1/07/202211:07:00 AM2
10Mike1/07/202211:18:00 AM1
11Sam7/01/202211:59:00 PM1
12Warner7/02/202212:05:00 AM1
Sheet1
Cell Formulas
RangeFormula
D2:D12D2=ROWS(UNIQUE(FILTER(A$2:A2,(C$2:C2>=C2-TIME(0,10,0))*(C$2:C2<=C2))))
 
Upvote 0
Solution
The second question was whether you will accept a formula like this that will only work in the versions I mentioned.
If not, then I will leave it to somebody else to battle with a much bigger formula.

Karthik23.xlsm
ABCD
1NamesDateTimeUnique Count
2John1/07/202210:00:00 AM1
3John1/07/202210:03:00 AM1
4Peter1/07/202210:05:00 AM2
5David1/07/202210:05:00 AM3
6Sam1/07/202210:05:00 AM4
7John1/07/202210:09:00 AM4
8David1/07/202211:00:00 AM1
9Jerry1/07/202211:07:00 AM2
10Mike1/07/202211:18:00 AM1
11Sam7/01/202211:59:00 PM1
12Warner7/02/202212:05:00 AM1
Sheet1
Cell Formulas
RangeFormula
D2:D12D2=ROWS(UNIQUE(FILTER(A$2:A2,(C$2:C2>=C2-TIME(0,10,0))*(C$2:C2<=C2))))
Big Thanks Peter!! Formulae worked like gem :) marked as solution
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top