Split list/table according to specified time gap

K

Kariba

Board Regular
Joined
Mar 15, 2023
Messages
59
Office Version
  1. 365
Platform
  1. Windows
Hi, hope you can help. I have a table with 19 columns (and 50k + rows) which changes 3 times a week. One of the columns is times. I need to divide this where there is at least a 20 minute gap from the row above. I was thinking of identifying in a new column T eg 1, 2, 3 etc but having trouble getting it to work.

On the table below, Row 2 & 5 could be group 1, Row 3 & 8 could be group 2 etc. An added complication is there can be no more than 6 in each group.

Book1
ABCDEFGHIJKLMNOPQRSTU
1ABCDEFGHIJKLMNOPQRST
201/01/202401/01/2024 06:0017
301/01/202401/01/2024 06:1017
401/01/202401/01/2024 06:1517
501/01/202401/01/2024 06:2017
601/01/202401/01/2024 06:2517
701/01/202401/01/2024 06:2517
801/01/202401/01/2024 06:3017
901/01/202401/01/2024 06:3017
1001/01/202401/01/2024 06:4017
1101/01/202401/01/2024 06:4017
1201/01/202401/01/2024 06:4517
1301/01/202401/01/2024 06:5517
1401/01/202401/01/2024 06:5517
1501/01/202401/01/2024 06:5517
1601/01/202401/01/2024 06:5517
1701/01/202401/01/2024 07:0017
1801/01/202401/01/2024 07:0017
Sheet1
Cell Formulas
RangeFormula
T2:T18T2=COUNTIFS($G$1:$G$1000,">="&TIME(0,20,0))
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi, Could you please share a sample result sheet? I didn't get where to write the newly created groups.
Is VBA ok?
 
Upvote 0
Hi, there wasn't a specific format yet as it's a new project I'm working on. Just grouped with 20 mins between them.

VBA is fine.

Even numbering them in groups in Column T would be a great help for now. Then I could sort or filter and move it manually if necessary.

Thanks
 
Upvote 0
I manually created the sort of result that would be OK. Essentially it's for sharing work out but they need a 20 min gap to complete and move on. On this example, I'd need 7 people.

Book1
ABCDEFGHIJKLMNOPQRST
1ABCDEFGHIJKLMNOPQRST
201/01/2024 00:0001/01/2024 06:001
301/01/2024 00:0001/01/2024 06:102
401/01/2024 00:0001/01/2024 06:153
501/01/2024 00:0001/01/2024 06:201
601/01/2024 00:0001/01/2024 06:254
701/01/2024 00:0001/01/2024 06:255
801/01/2024 00:0001/01/2024 06:302
901/01/2024 00:0001/01/2024 06:306
1001/01/2024 00:0001/01/2024 06:401
1101/01/2024 00:0001/01/2024 06:403
1201/01/2024 00:0001/01/2024 06:454
1301/01/2024 00:0001/01/2024 06:552
1401/01/2024 00:0001/01/2024 06:555
1501/01/2024 00:0001/01/2024 06:556
1601/01/2024 00:0001/01/2024 06:557
1701/01/2024 00:0001/01/2024 07:001
1801/01/2024 00:0001/01/2024 07:003
Sheet1
 
Upvote 0
Not really, that's just the current table format. It only contains the date that is provided as well in column G. However, I can't change the format so every update would come in like that and would need to be adjusted to suit.
 
Upvote 0
I think this should work. T2:
Excel Formula: 
=IF(COUNTIF(G2:G$18,"<="&G2+TIME(0,20,0))>6,6,COUNTIF(G2:G$18,"<="&G2+TIME(0,20,0)))
 
Upvote 0
I'm getting the result shown in column U. It's very close, but if 6 was a person, they wouldn't be able to complete all those tasks in time

Book1
ABCDEFGHIJKLMNOPQRSTU
1ABCDEFGHIJKLMNOPQRST
201/01/2024 00:0001/01/2024 06:0014
301/01/2024 00:0001/01/2024 06:1026
401/01/2024 00:0001/01/2024 06:1536
501/01/2024 00:0001/01/2024 06:2016
601/01/2024 00:0001/01/2024 06:2546
701/01/2024 00:0001/01/2024 06:2556
801/01/2024 00:0001/01/2024 06:3025
901/01/2024 00:0001/01/2024 06:3064
1001/01/2024 00:0001/01/2024 06:4016
1101/01/2024 00:0001/01/2024 06:4036
1201/01/2024 00:0001/01/2024 06:4546
1301/01/2024 00:0001/01/2024 06:5526
1401/01/2024 00:0001/01/2024 06:5555
1501/01/2024 00:0001/01/2024 06:5564
1601/01/2024 00:0001/01/2024 06:5573
1701/01/2024 00:0001/01/2024 07:0012
1801/01/2024 00:0001/01/2024 07:0031
Sheet1
Cell Formulas
RangeFormula
U2:U18U2=IF(COUNTIF(G2:G$18,"<="&G2+TIME(0,20,0))>6,6,COUNTIF(G2:G$18,"<="&G2+TIME(0,20,0)))
 
Upvote 0
Oh OK. Maybe I didn't explain it well. These are all jobs that I need to divide up. Each job takes 20 mins.

So the person doing 0600 in G2 cannot do something else until 0620 in G5. Another person needs to do G3 and then free to do G8 and so on. So wanted to number them as person 1, person 2 etc. Then I can sort that column.

Open to other suggestions if you think of any other way.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
172
sanrama
S
A
  • Solved
Number of Weeks Between First and Last of a List of Dates
Replies
7
Views
162
hagia_sofia
hagia_sofia
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
286
excelnewbie999
E
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
530
Alex Blakenburg
A

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

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