How to create a specified number of rows for each date?

[alexcr]

Let's say that in Cells A1-A7 there are seven dates, and in Cells B1-B7 there are the number of rows you'd like per date.

From A9 downward, you'd like to list each date the number of times specified. For example...

• If Cell A1 contains "7/12/21" and Cell B1 contains "3", then Cells A9-A11 would all be "7/12/21".

• If Cell A2 contains "7/15/21" and Cell B2 contains "2", then Cells A12-A13 would all be "7/15/21".

• If Cell A3 contains "7/18/21" and Cell B3 contains "0", then this date would be skipped.

• And so on.

Is there a formula or formulas that can automatically generate these cells/rows? Thanks in advance!

 

[Crystalyzer]

Place this formula in cell A9 and copy it down the number of rows that SUM(B1:B7) produces

=IFS(ROW()-8<=$B$1,$A$1,ROW()-(8+$B$1)<=$B$2,$A$2,ROW()-(8+SUM($B$1:$B$2))<=$B$3,$A$3,ROW()-(8+SUM($B$1:$B$3))<=$B$4,$A$4,ROW()-(8+SUM($B$1:$B$4))<=$B$5,$A$5,ROW()-(8+SUM($B$1:$B$5))<=$B$6,$A$6,ROW()-(8+SUM($B$1:$B$6))<=$B$7,$A$7,TRUE,"")

 

[alexcr]

Place this formula in cell A9 and copy it down the number of rows that SUM(B1:B7) produces

=IFS(ROW()-8<=$B$1,$A$1,ROW()-(8+$B$1)<=$B$2,$A$2,ROW()-(8+SUM($B$1:$B$2))<=$B$3,$A$3,ROW()-(8+SUM($B$1:$B$3))<=$B$4,$A$4,ROW()-(8+SUM($B$1:$B$4))<=$B$5,$A$5,ROW()-(8+SUM($B$1:$B$5))<=$B$6,$A$6,ROW()-(8+SUM($B$1:$B$6))<=$B$7,$A$7,TRUE,"")

Thanks, @Crystalyzer. But is there any way to do this dynamically so that I don't have to (figure out how many rows are needed and then) copy and paste the formula?

 

[lrobbo314]

Here is a way using Power Query.

Book1
	A	B
1	Date	X
2	7/12/2021	2
3	7/13/2021	0
4	7/14/2021	5
5	7/15/2021	7
6	7/16/2021	2
7	7/17/2021	4
8	7/18/2021	4
9
10	Date
11	7/12/2021
12	7/12/2021
13	7/14/2021
14	7/14/2021
15	7/14/2021
16	7/14/2021
17	7/14/2021
18	7/15/2021
19	7/15/2021
20	7/15/2021
21	7/15/2021
22	7/15/2021
23	7/15/2021
24	7/15/2021
25	7/16/2021
26	7/16/2021
27	7/17/2021
28	7/17/2021
29	7/17/2021
30	7/17/2021
31	7/18/2021
32	7/18/2021
33	7/18/2021
34	7/18/2021
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Rows = Table.TransformColumns(Source,{{"X", each {1.._}}}),
    Expand = Table.ExpandListColumn(Rows, "X"),
    noNull = Table.SelectRows(Expand, each ([X] <> null)),
    RC = Table.RemoveColumns(noNull,{"X"}),
    toDate = Table.TransformColumnTypes(RC,{{"Date", type date}})
in
    toDate

And here is a way using formulas.

Book1
	A	B
1	7/12/2021	2
2	7/13/2021	0
3	7/14/2021	5
4	7/15/2021	7
5	7/16/2021	2
6	7/17/2021	4
7	7/18/2021	4
8
9	7/12/2021
10	7/12/2021
11	7/14/2021
12	7/14/2021
13	7/14/2021
14	7/14/2021
15	7/14/2021
16	7/15/2021
17	7/15/2021
18	7/15/2021
19	7/15/2021
20	7/15/2021
21	7/15/2021
22	7/15/2021
23	7/16/2021
24	7/16/2021
25	7/17/2021
26	7/17/2021
27	7/17/2021
28	7/17/2021
29	7/18/2021
30	7/18/2021
31	7/18/2021
32	7/18/2021
Sheet5

Cell Formulas
Range	Formula
A9:A32	A9	=LET(d,A1:A7,s,SUBSTITUTE(TEXTJOIN(",",1,REPT(ROW(d)&",",B1:B7)),",,",","),se,LEFT(s,LEN(s)-1),f,FILTERXML("<x><y>" & SUBSTITUTE(se,",","</y><y>") & "</y></x>","//y"),INDEX(d,f))
Dynamic array formulas.

 

[JoeMo]

Another way using VBA.

Book1
	A	B
1	7/12/2021	3
2	7/15/2021	2
3	7/18/2021	0
4	7/21/2021	1
5	7/24/2021	2
6	7/27/2021	3
7	7/30/2021	4
8
9	7/12/2021
10	7/12/2021
11	7/12/2021
12	7/15/2021
13	7/15/2021
14	7/21/2021
15	7/24/2021
16	7/24/2021
17	7/27/2021
18	7/27/2021
19	7/27/2021
20	7/30/2021
21	7/30/2021
22	7/30/2021
23	7/30/2021
Sheet1

Sub RowsPerDate()
'assumes input dates begin in A1
Dim Rin As Range, NxRw As Long, c As Range
Set Rin = Range("A1").CurrentRegion
NxRw = Rin.Columns(1).Cells.Count + 2
Application.ScreenUpdating = False
For Each c In Rin.Columns(1).Cells
If c.Offset(0, 1).Value > 0 Then
    Cells(NxRw, 1).Resize(c.Offset(0, 1).Value, 1).Value = c.Value
    NxRw = Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
Next c
Application.ScreenUpdating = True
End Sub

 

[alexcr]

And here is a way using formulas.

Thank you, @lrobbo314! This dynamic array formula works perfectly.

And thanks to everyone else who responded, too.

 

[alexcr]

Thank you, @lrobbo314! This dynamic array formula works perfectly.

And thanks to everyone else who responded, too.

@lrobbo314 The only issue I'm having is figuring out how the dynamic array formula should be adjusted if the A1:B7 range starts on a different row and/or a different column, i.e. not from A1 by from, say, B19.

I've adjusted the formula so that it's still referencing those two columns, in their new location. But the formula nevertheless seems to be broken.

 

[lrobbo314]

Needed to add a bit to the formula to make it work on any row. I highlighted the change in the formula below.

Book1
	E	F	G
10	7/12/2021	2
11	7/13/2021	0
12	7/14/2021	5
13	7/15/2021	7
14	7/16/2021	2
15	7/17/2021	4
16	7/18/2021	4
17
18
19
20			7/12/2021
21			7/12/2021
22			7/14/2021
23			7/14/2021
24			7/14/2021
25			7/14/2021
26			7/14/2021
27			7/15/2021
28			7/15/2021
29			7/15/2021
30			7/15/2021
31			7/15/2021
32			7/15/2021
33			7/15/2021
34			7/16/2021
35			7/16/2021
36			7/17/2021
37			7/17/2021
38			7/17/2021
39			7/17/2021
40			7/18/2021
41			7/18/2021
42			7/18/2021
43			7/18/2021
Sheet1

Cell Formulas
Range	Formula
G20:G43	G20	=LET(d,E10:E16,s,SUBSTITUTE(TEXTJOIN(",",1,REPT(ROW(d)-ROW(E10)+1&",",F10:F16)),",,",","),se,LEFT(s,LEN(s)-1),f,FILTERXML("<x><y>" & SUBSTITUTE(se,",","</y><y>") & "</y></x>","//y"),INDEX(d,f))
Dynamic array formulas.

 

[alexcr]

Perfect, @lrobbo314. Thank you again!

 

[alexcr]

@lrobbo314, this suddenly isn't working, and I can't figure out why. Do you see anything that looks off to you...?