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!

 

[Fluff]

You cannot use the filterxml function on a Mac, it only exists on a PC.

 

[lrobbo314]

So glad you caught that Fluff. I was about to go down a rabbit hole trying to figure out why that wasn't working.

Do Macs get Power Query?

 

[Fluff]

Do Macs get Power Query?

Don't think so, but not sure.

 

[Fluff]

If you need it to work on a Mac, how about

+Fluff 1.xlsm
	A	B	C	D
1
2
3
4
5
6
7
8			17/07/2021	1
9	17/07/2021		18/07/2021	2
10	18/07/2021		19/07/2021	0
11	18/07/2021		20/07/2021	0
12	21/07/2021		21/07/2021	3
13	21/07/2021		22/07/2021	1
14	21/07/2021		23/07/2021	4
15	22/07/2021		24/07/2021	7
16	23/07/2021
17	23/07/2021
18	23/07/2021
19	23/07/2021
20	24/07/2021
21	24/07/2021
22	24/07/2021
23	24/07/2021
24	24/07/2021
25	24/07/2021
26	24/07/2021
27
Main

Cell Formulas
Range	Formula
A9:A26	A9	=LET(Rng,C8:C15,Txt,"|"&TEXTJOIN(,,REPT(ROW(Rng)-MIN(ROW(Rng))+1&"|",D8:D15)),Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt,"|","")))-1),Ary,REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,"|","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt,"|","~",Qty)),""),INDEX(Rng,Ary))
Dynamic array formulas.

 

[alexcr]

Thanks, @Fluff. This works in Mac.

@lrobbo314, the odd thing is that the formula stopped working in Windows, as well. (I have both Mac and Windows PCs, and use Excel on both.) Maybe it's because I opened the file on a Mac and subsequently opened it in Windows, and that somehow corrupted the formula and/or workbook? Not sure.

 

[Fluff]

How many rows were you trying to fill when it failed on Windows?

 

[alexcr]

How many rows were you trying to fill when it failed on Windows?

I believe around 78.

 

[Fluff]

That number of rows shouldn't have caused a problem, so not sure why it failed.