Repeat value certain number of times

[broion]

Hi Guys!

I'm having a hard time creating a formula to extract data based from this.

This is what's showing in one of the sheets:

8:30 PM 4.00
8:45 PM
9:00 PM 2.00

I would want to paste that on the next sheet which should look like this:

Mon
Shift Time
Emp 1 8:30 PM
Emp 2 8:30 PM
Emp 3 8:30 PM
Emp 4 8:30 PM
Emp 5 9:00 PM
Emp 6 9:00 PM

Basically, the next sheet will paste the time on column 2 four times if the first sheet's column 2 will have a value >0

 

[Rick Rothstein]

Assuming your output is going to Sheet2, run this macro with your data sheet active...

[table="width: 500"]
[tr]
	[td]Sub ExpandEmployeeShiftTimeTable()
  Dim R As Long, Cnt As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim Result(1 To Application.Sum(Columns("B")), 1 To 2)
  For R = 1 To UBound(Data)
    If Data(R, 2) > 1 Then
      For Cnt = 1 To Data(R, 2)
        X = X + 1
        Result(X, 1) = "Emp " & X
        Result(X, 2) = Format$(Data(R, 1), "h:mm AM/PM")
      Next
    End If
  Next
  Sheets("Sheet2").Range("A1:B2") = [{"Mon","";"Shift","Time"}]
  Sheets("Sheet2").Range("A3").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub[/td]
[/tr]
[/table]

 

[broion]

Wow! That was great! Works good for me. However, my problem now is that this is a big spreadsheet. Sheet 1 actually contains 3 tables that I want to paste on the 2nd sheet. I can't post screenshot as I am at work and no admin rights to install the external apps recommended in your signature.

Is it possible to use a formula for this, or Macro is needed?

First table shows the shift time, 2nd table shows the lunch break for 1 hour then last table shows the 15-min break scheds.

Then the table has a 15-min interval from 12:00 AM to 11:45 PM

Sorry if this is a bit unclear.

 

[Rick Rothstein]

Wow! That was great! Works good for me. However, my problem now is that this is a big spreadsheet. Sheet 1 actually contains 3 tables that I want to paste on the 2nd sheet. I can't post screenshot as I am at work and no admin rights to install the external apps recommended in your signature.

Is it possible to use a formula for this, or Macro is needed?

First table shows the shift time, 2nd table shows the lunch break for 1 hour then last table shows the 15-min break scheds.

Then the table has a 15-min interval from 12:00 AM to 11:45 PM

Sorry if this is a bit unclear.

Completely unclear... at least to me. I think you will have to show us the before tables... or at least describe them in enough detail for us to be able to duplicate examples of them for ourselves... and then show us what you wanted the output to look like. By the way, as an aside, simplifying a problem for us will almost always get you a solution that you cannot use nor adapt for your actual situation.

 

[broion]

Got you, Rick. Will work on getting to screenshot for better understanding.

 

[broion]

Hi Rick! Will just wait till I get home to post the screenshots as I don't have admin rights to install external apps here at work,

 

[broion]

I'm back!

So, I'm working on this excel file. This is the first sheet where I am calculating staffing based on forecasted volume:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	*	*	Staffed for the day					*	*	*	Lunch break
3	Intervals	*	Mon	Tue	Wed	Thu	Fri	*	Intervals	*	Mon	Tue	Wed	Thu	Fri
4	8:00 PM	*	*	*	*	*	*	*	8:00 PM	*	*	*	*	*	*
5	8:15 PM	*	*	*	*	*	*	*	8:15 PM	*	*	*	*	*	*
6	8:30 PM	4.00	4.00	4.00	4.00	4.00	*	*	8:30 PM	*	*	*	*	*	*
7	8:45 PM	*	*	*	*	*	*	*	8:45 PM	*	*	*	*	*	*
8	9:00 PM	2.00	2.00	2.00	2.00	2.00	*	*	9:00 PM	*	*	*	*	*	*
9	9:15 PM	*	*	*	*	*	*	*	9:15 PM	*	*	*	*	*	*
10	9:30 PM	3.00	3.00	3.00	3.00	3.00	*	*	9:30 PM	*	*	*	*	*	*
11	9:45 PM	*	*	*	*	*	*	*	9:45 PM	*	*	*	*	*	*
12	10:00 PM	*	*	*	*	*	*	*	10:00 PM	*	*	*	*	*	*
13	10:15 PM	*	*	*	*	*	*	*	10:15 PM	*	*	*	*	*	*
14	10:30 PM	*	*	*	*	*	*	*	10:30 PM	*	*	*	*	*	*
15	10:45 PM	*	*	*	*	*	*	*	10:45 PM	*	*	*	*	*	*
16	11:00 PM	2.00	2.00	2.00	2.00	2.00	*	*	11:00 PM	*	*	*	*	*	*
17	11:15 PM	*	*	*	*	*	*	*	11:15 PM	*	*	*	*	*	*
18	11:30 PM	*	*	*	*	*	*	*	11:30 PM	*	*	*	*	*	*
19	11:45 PM	*	*	*	*	*	*	*	11:45 PM	*	*	*	*	*	*
20	12:00 AM	*	*	*	*	*	*	*	12:00 AM	3.00	3.00	3.00	3.00	3.00	*
Staffing

And paste the analyzed schedules on the next sheet:

Excel Workbook
	A	B	C	D	E
1	*	Mon
2		Shift Time	Brk 1	Lunch	Brk 2
3	Emp 1	8:30 PM	*	12:00 AM	*
4	Emp 2	8:30 PM	*	12:00 AM	*
5	Emp 3	8:30 PM	*	12:00 AM	*
6	Emp 4	8:30 PM	*	*	*
7	Emp 5	9:00 PM	*	*	*
8	Emp 6	9:00 PM	*	*	*
9	Emp 7	9:30 PM	*	*	*
10	Emp 8	9:30 PM	*	*	*
11	Emp 9	9:30 PM	*	*	*
12	Emp 10	11:00 PM	*	*	*
13	Emp 11	11:00 PM	*	*	*
schedule

Basically, I would want the second sheet to show the schedules based from the first sheet whereas the first sheet showed 4 employees scheduled at 08:30 PM, so on and so forth. Is this possible to be done via formula? Or do I have to enter VB code/macro for this to work? Hope this clears it up. Thanks in advance.

 

[bosco_yip]

Try......

1] Assume your sheet1 named "Staffing", sheet2 named "schedule"


2] In "schedule sheet" Shift Time column B3, formula copy down until blank :


=IFERROR(INDEX(Staffing!B$4:B$20,MATCH(1,INDEX(--(COUNTIF(B$2:B2,Staffing!B$4:B$20)< Staffing!C$4:C$20),0),0)),"")


3] In "schedule sheet" Lunch column D3, formula copy down until blank :


=IFERROR(INDEX(Staffing!J$4:J$20,MATCH(1,INDEX(--(COUNTIF(D$2:D2,Staffing!J$4:J$20)< Staffing!K$4:K$20),0),0)),"")


Regards
Bosco

 

[broion]

Thanks, Bosco! This worked perfectly!

 

[broion]

Hi All,

I have another problem in factoring the break schedules. My worksheet only has 1 table for both 1st and second breaks. Say, I only have 4 employees scheduled for the day. With the formula provided by Bosco, I am unable to identify the second break. Any workaround on this?

Excel Workbook
	A	B	C	D	E	F
1	*	15-min break
2	Intervals	Mon	Tue	Wed	Thu	Fri
3	Time
4	10:30 PM	1.00	*	*	*	1.00
5	10:45 PM	1.00	*	1.00	*	1.00
6	11:00 PM	1.00	1.00	1.00	1.00	1.00
7	11:15 PM	1.00	1.00	1.00	1.00	1.00
8	11:30 PM	*	1.00	1.00	1.00	*
9	11:45 PM	*	1.00	*	1.00	*
10	12:00 AM	*	*	*	*	*
11	12:15 AM	*	*	*	*	1.00
12	12:30 AM	*	*	*	*	1.00
13	12:45 AM	1.00	*	1.00	*	1.00
14	1:00 AM	1.00	*	1.00	*	1.00
15	1:15 AM	1.00	1.00	1.00	*	*
16	1:30 AM	1.00	1.00	1.00	1.00	*
17	1:45 AM	*	1.00	*	1.00	*
18	2:00 AM	*	1.00	*	1.00	*
19	2:15 AM	*	*	*	1.00	*
Sheet1

This is where the formula should auto populate here

Excel Workbook
	A	B	C	D	E
1	*	Mon
2		Shift Time	Brk 1	Lunch	Brk 2
3	Emp 1	*	*	*	*
4	Emp 2	*	*	*	*
5	Emp 3	*	*	*	*
6	Emp 4	*	*	*	*
Sheet2