Enter data into column based in time interval in adjacent column

David Kirker

New Member
Joined
Jun 11, 2018
Messages
1
My goal is to create a schedule using the fewest number of people as possible while maintaining a minimum interval between procedures. I start by adding the letter ‘A’ in the f.name column and continue until I see an interval in the time column that is less than desired (3 to 5 min, depending). I will add subsequent letters (B, C, D, E, etc.) as needed but always default to the first letters where possible. I then resort by f.name and time to create shifts for actual people. I am happy to pay for this code but my first question is. CAN THIS BE DONE? Second, what might this code look like? I have limited experience with VBA but no longer have time to research the code. Any help is greatly appreciated. I'm using excel 2013 on Office 365. I expect the upgrade to 2016 soon.
This is the sheet I use:
NUMINTT.S.PROCEDURET.P.TIMEF.NAMEL.NAME
1BC (1 Tube)0.25 HR06:15A 00:07
2BC (1 Tube)0.25 HR06:22A 00:07
3BC (1 Tube)0.25 HR06:29A 00:01
1BC (1 Tube)0.5 HR06:30B 00:06
4BC (1 Tube)0.25 HR06:36A 00:01
2BC (1 Tube)0.5 HR06:37B 00:06
5BC (1 Tube)0.25 HR06:43A 00:01
3BC (1 Tube)0.5 HR06:44B 00:01
1BC (1 Tube)0.75 HR06:45C 00:05
6BC (1 Tube)0.25 HR06:50A 00:01
4BC (1 Tube)0.5 HR06:51B 00:01
2BC (1 Tube)0.75 HR06:52C 00:05
7BC (1 Tube)0.25 HR06:57A 00:01
5BC (1 Tube)0.5 HR06:58B 00:01
3BC (1 Tube)0.75 HR06:59C 00:01
1BC (1 Tube)1 HR07:00A 00:04
8BC (1 Tube)0.25 HR07:04B 00:01
6BC (1 Tube)0.5 HR07:05C 00:01
4BC (1 Tube)0.75 HR07:06A 00:01
2BC (1 Tube)1 HR07:07B 00:04
9BC (1 Tube)0.25 HR07:11C 00:01
7BC (1 Tube)0.5 HR07:12A 00:01
5BC (1 Tube)0.75 HR07:13B 00:01
3BC (1 Tube)1 HR07:14C 00:04
10BC (1 Tube)0.25 HR07:18A 00:01
8BC (1 Tube)0.5 HR07:19B 00:01

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

lotus%20example%20A.JPG

This is the product, where I have failed to meet the minimum interval:

NUMINTT.S.PROCEDURET.P.TIMEF.NAMEL.NAME
1BC (1 Tube)0.25 HR06:15A 00:07
2BC (1 Tube)0.25 HR06:22A 00:07
3BC (1 Tube)0.25 HR06:29A 00:07
4BC (1 Tube)0.25 HR06:36A 00:07
5BC (1 Tube)0.25 HR06:43A 00:07
6BC (1 Tube)0.25 HR06:50A 00:07
7BC (1 Tube)0.25 HR06:57A 00:03
1BC (1 Tube)1 HR07:00A 00:06
4BC (1 Tube)0.75 HR07:06A 00:06
7BC (1 Tube)0.5 HR07:12A 00:06
10BC (1 Tube)0.25 HR07:18A 00:03
4BC (1 Tube)1 HR07:21A 00:06
7BC (1 Tube)0.75 HR07:27A 00:05
12BC (1 Tube)0.25 HR07:32A 00:03
6BC (1 Tube)1 HR07:35A 00:05
11BC (1 Tube)0.5 HR07:40A 00:04
3BC (1 Tube)1.5 HR07:44A 00:04
10BC (1 Tube)0.75 HR07:48A 00:05
15BC (1 Tube)0.25 HR07:53A 00:03
9BC (1 Tube)1 HR07:56A 00:04
1BC (1 Tube)2 HR08:00A 00:03
10BC (1 Tube)1 HR08:03A 00:04
2BC (1 Tube)2 HR08:07A 00:03
11BC (1 Tube)1 HR08:10A 00:04
3BC (1 Tube)2 HR08:14A 00:03
12BC (1 Tube)1 HR08:17A 00:04
19BC (1 Tube)0.25 HR08:21A 00:03
13BC (1 Tube)1 HR08:24A 00:04
20BC (1 Tube)0.25 HR08:28A 00:02

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
lotus%20example%202.JPG
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Oscar Cronquist

Board Regular
Joined
Apr 20, 2018
Messages
200
A
B
C
D
E
1
TimeNameIntervalNames
2
6:15​
AA
3
6:22​
AB
4
6:29​
AC
5
6:30​
BD
6
6:36​
AE
7
6:37​
BF
8
6:43​
AG
9
6:44​
B
10
6:45​
C
11
6:50​
A
12
6:51​
B
13
6:52​
C
14
6:57​
A
15
6:58​
B
16
6:59​
C
17
7:00​
D
18
7:04​
A
19
7:05​
B
20
7:06​
C
21
7:07​
D
22
7:11​
A
23
7:12​
B
24
7:13​
C
25
7:14​
D
26
7:18​
A
27
7:19​
B
Sheet: Sheet3

I entered A in cell B2.

The following array formula seems to work.
Array formula in cell B3:
=INDEX($E$2:$E$8,MATCH(0,FREQUENCY(IF(A3-$A$2:A2<=(5/1440),MATCH($B$2:B2,$E$2:$E$8,0),99999),MATCH($E$2:$E$8,$E$2:$E$8,0)),0))

How to enter an array formula
1. Double click on cell B3
2. Copy / Paste formula to cell B3
3. Press and hold CTRL + SHIFT
4. Press Enter
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,154
Latest member
gm_jagath

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
Top