Reading a quantity in a cell and separating into rows... kind of hard to explain

[sparanthony]

Is there a formula where it can somehow automatically read the quantity in a cell that I need of each team name, and list them in a new column.
For example
Column 1 has the quantity: 7
Column 2 has the team name: Sharks
Column 3 has sport: hockey
Column 4 has school: Sample HS

I need the quantity listed for the whole row to separate 7 times. See photo for example.

Any help is greatly appreciated!

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Qty	PostCode	In Use?	Latitude
2	3	BN1 7GB	Yes	50.84139		BN1 7GB	Yes	50.84139
3	2	CB25 0DU	Yes	52.27783		BN1 7GB	Yes	50.84139
4	1	LS13 3DJ	Yes	53.80972		BN1 7GB	Yes	50.84139
5	0	SO51 7SA	Yes	50.99463		CB25 0DU	Yes	52.27783
6	2	ST16 3QU	Yes	52.81398		CB25 0DU	Yes	52.27783
7	0	E5 0QG	Yes	51.55804		LS13 3DJ	Yes	53.80972
8	0	SG13 7FZ	Yes	51.80031		ST16 3QU	Yes	52.81398
9	3	SN2 1BD	Yes	51.56901		ST16 3QU	Yes	52.81398
10	4	TW3 1NN	Yes	51.47086		SN2 1BD	Yes	51.56901
11	2	SL6 4ED	Yes	51.51571		SN2 1BD	Yes	51.56901
12						SN2 1BD	Yes	51.56901
13						TW3 1NN	Yes	51.47086
14						TW3 1NN	Yes	51.47086
15						TW3 1NN	Yes	51.47086
16						TW3 1NN	Yes	51.47086
17						SL6 4ED	Yes	51.51571
18						SL6 4ED	Yes	51.51571
19
Data

Cell Formulas
Range	Formula
F2:H18	F2	=LET(rs,SCAN(0,A2:A11,LAMBDA(a,b,a+b)),INDEX(B2:D11,XMATCH(SEQUENCE(SUM(A2:A11)),rs,1),{1,2,3}))
Dynamic array formulas.

 

[sparanthony]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Qty	PostCode	In Use?	Latitude
2	3	BN1 7GB	Yes	50.84139		BN1 7GB	Yes	50.84139
3	2	CB25 0DU	Yes	52.27783		BN1 7GB	Yes	50.84139
4	1	LS13 3DJ	Yes	53.80972		BN1 7GB	Yes	50.84139
5	0	SO51 7SA	Yes	50.99463		CB25 0DU	Yes	52.27783
6	2	ST16 3QU	Yes	52.81398		CB25 0DU	Yes	52.27783
7	0	E5 0QG	Yes	51.55804		LS13 3DJ	Yes	53.80972
8	0	SG13 7FZ	Yes	51.80031		ST16 3QU	Yes	52.81398
9	3	SN2 1BD	Yes	51.56901		ST16 3QU	Yes	52.81398
10	4	TW3 1NN	Yes	51.47086		SN2 1BD	Yes	51.56901
11	2	SL6 4ED	Yes	51.51571		SN2 1BD	Yes	51.56901
12						SN2 1BD	Yes	51.56901
13						TW3 1NN	Yes	51.47086
14						TW3 1NN	Yes	51.47086
15						TW3 1NN	Yes	51.47086
16						TW3 1NN	Yes	51.47086
17						SL6 4ED	Yes	51.51571
18						SL6 4ED	Yes	51.51571
19
Data

Cell Formulas
Range	Formula
F2:H18	F2	=LET(rs,SCAN(0,A2:A11,LAMBDA(a,b,a+b)),INDEX(B2:D11,XMATCH(SEQUENCE(SUM(A2:A11)),rs,1),{1,2,3}))
Dynamic array formulas.

Hello, thank for getting back to me quickly. It breaks it out but i'm getting the #NAME? error. I probably have to input my appropriate fields but i can't seem to figure that part out.

 

[Fluff]

Do you have the Scan function?

 

[sparanthony]

Do you have the Scan function?

I don't have the SCAN function

 

[Fluff]

Ok, how about

=LET(Rng,A2:A11,r,ROWS(Rng),mm,MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),Rng),INDEX(B2:D11,XMATCH(SEQUENCE(SUM(Rng)),mm,1),{1,2,3}))

 

[sparanthony]

Ok, how about

=LET(Rng,A2:A11,r,ROWS(Rng),mm,MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),Rng),INDEX(B2:D11,XMATCH(SEQUENCE(SUM(Rng)),mm,1),{1,2,3}))

Yes, perfect! that worked!

thanks a lot!

 

[Fluff]

You're welcome & thanks for the feedback.