Layered return values in Excel sheet

[SeanyD]

Hello,
i am looking for a formula to return the values like in the table. The values in column A and B are manually entered and want the return values in D,E,F,G,H to populate automatically. I need to start each column where the previous one ends. I've tried IF(ISBLANK... in columns E,F,G,H but i get a #SPILL error. I want to try to stay away from macros- that is currently beyond me.

Any assistance is greatly appreciated

 

[lrobbo314]

How about this?

EXCEL
	A	B	C	D	E	F	G	H
1	Type	Count
2	Red Car	5		Red Car
3	Red Van	4		Red Car
4	Blue Car	4		Red Car
5	White Car	2		Red Car
6	White Van	7		Red Car
7					Red Van
8					Red Van
9					Red Van
10					Red Van
11						Blue Car
12						Blue Car
13						Blue Car
14						Blue Car
15							White Car
16							White Car
17								White Van
18								White Van
19								White Van
20								White Van
21								White Van
22								White Van
23								White Van
Sheet1

Cell Formulas
Range	Formula
D2:H23	D2	=IFERROR(DROP(REDUCE("",SEQUENCE(ROWS(A2:A6)),LAMBDA(s,c,HSTACK(s,INDEX(A2:A6,VSTACK(INDEX("",SEQUENCE(ROWS(s),,,0)),SEQUENCE(INDEX(B2:B6,c),,c,0)))))),1,1),"")
Dynamic array formulas.

 

[SeanyD]

That is Brilliant. Thank you very much Irobbo314. Exactly what i am looking for

 

[Peter_SSs]

Welcome to the MrExcel board!

I was trying for a shorter way but only succeeded by a few characters.
Anyway, this is what I came up with.

24 10 08.xlsm
	A	B	C	D	E	F	G	H
1	Type	Count
2	Red Car	5		Red Car
3	Red Van	4		Red Car
4	Blue Car	4		Red Car
5	White Car	2		Red Car
6	White Van	7		Red Car
7					Red Van
8					Red Van
9					Red Van
10					Red Van
11						Blue Car
12						Blue Car
13						Blue Car
14						Blue Car
15							White Car
16							White Car
17								White Van
18								White Van
19								White Van
20								White Van
21								White Van
22								White Van
23								White Van
Layers

Cell Formulas
Range	Formula
D2:H23	D2	=LET(n,B2:B6,s,SUM(n),x,VSTACK(0,BYROW(n,LAMBDA(q,SUM(B2:q)))),MAKEARRAY(s,ROWS(n),LAMBDA(r,c,IF(AND(r>INDEX(x,c),r<=INDEX(x,c+1)),INDEX(A2:A6,c),""))))
Dynamic array formulas.

 

[lrobbo314]

For fun, here is a way to do it with Power Query.

PQ
	A	B	C	D	E	F	G	H
1	Type	Count		Red Car	Red Van	Blue Car	White Car	White Van
2	Red Car	5		Red Car
3	Red Van	4		Red Car
4	Blue Car	4		Red Car
5	White Car	2		Red Car
6	White Van	7		Red Car
7					Red Van
8					Red Van
9					Red Van
10					Red Van
11						Blue Car
12						Blue Car
13						Blue Car
14						Blue Car
15							White Car
16							White Car
17								White Van
18								White Van
19								White Van
20								White Van
21								White Van
22								White Van
23								White Van
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    rSum = List.Generate(()=>[x=0,y=-1],
        each [y]<Table.RowCount(Source)-1,
        each [y = [y]+1,x=[x]+Source[Count]{y}],
        each [x]
    ),
    toTable = Table.FromColumns(Table.ToColumns(Source) & {rSum}),
    Pad = Table.TransformRows(toTable, each List.Repeat({""},[Column3]) & List.Repeat({[Column1]},[Column2])),
    Final = Table.FromColumns(Pad,Source[Type])
in
    Final