Stack value x times formula

[Kra]

Hi all!

I have a range of values in column B (starting from row 3, the length cannot be determined) and number which indicates how many times the value should be repeated in column C. I need to somehow convert it to a single column like in column F. Is it possible to achieve it with formula that will not cause my laptop to explode?

Book1
	A	B	C	D	E	F	G	H
1
2		List	Repeat			Result
3		Value1	2			Value1
4		Value2	3			Value1
5		Value3	1			Value2
6		Value4	5			Value2
7		Value5	0			Value2
8		Value6	2			Value3
9						Value4
10						Value4
11						Value4
12						Value4
13						Value4
14						Value6
15						Value6
16
17
Sheet1

 

[Jimmypop]

Hi there

You can use a helper column to achieve this... Or maybe there is another way but below is simple and effective...

Book1
	A	B	C	D	E	F
1
2	Helper	List	Repeat			Result
3	1	Value1	2			Value1
4	3	Value2	3			Value1
5	6	Value3	1			Value2
6	7	Value4	5			Value2
7	12	Value5	0			Value2
8	12	Value6	5			Value3
9						Value4
10						Value4
11						Value4
12						Value4
13						Value4
14						Value6
15						Value6
16						Value6
17						Value6
18						Value6
Sheet1

Cell Formulas
Range	Formula
A4:A8	A4	=A3+C3
F3:F18	F3	=IF(VLOOKUP(ROW(1:1),Table1[[Helper]:[List]],2)=0,"",VLOOKUP(ROW(1:1),Table1[[Helper]:[List]],2))

 

[Kra]

Hi there

You can use a helper column to achieve this... Or maybe there is another way but below is simple and effective...

Book1
	A	B	C	D	E	F
1
2	Helper	List	Repeat			Result
3	1	Value1	2			Value1
4	3	Value2	3			Value1
5	6	Value3	1			Value2
6	7	Value4	5			Value2
7	12	Value5	0			Value2
8	12	Value6	5			Value3
9						Value4
10						Value4
11						Value4
12						Value4
13						Value4
14						Value6
15						Value6
16						Value6
17						Value6
18						Value6
Sheet1

Cell Formulas
Range	Formula
A4:A8	A4	=A3+C3
F3:F18	F3	=IF(VLOOKUP(ROW(1:1),Table1[[Helper]:[List]],2)=0,"",VLOOKUP(ROW(1:1),Table1[[Helper]:[List]],2))

Thanks! I cannot use tables sadly, it needs to be plain values

 

[Jimmypop]

Thanks! I cannot use tables sadly, it needs to be plain values

You can just use it without being a table as well... Will update just formula as soon as back at pc

 

[jasonb75]

Here's another way using dynamic arrays if your version of excel has all of the necessary functions.

Book1
	A	B	C	D	E	F
1
2		List	Repeat			Result
3		Value1	2			Value1
4		Value2	3			Value1
5		Value3	1			Value2
6		Value4	5			Value2
7		Value5	0			Value2
8		Value6	5			Value3
9						Value4
10						Value4
11						Value4
12						Value4
13						Value4
14						Value6
15						Value6
16						Value6
17						Value6
18						Value6
Sheet1

Cell Formulas
Range	Formula
F3:F18	F3	=TRANSPOSE(TEXTSPLIT(CONCAT(REPT(B3:B8&",",C3:C8)),",",,1))
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1
2		List	Repeat
3		Value1	2		Value1
4		Value2	3		Value1
5		Value3	1		Value2
6		Value4	5		Value2
7		Value5	0		Value2
8		Value6	2		Value3
9					Value4
10					Value4
11					Value4
12					Value4
13					Value4
14					Value6
15					Value6
Main

Cell Formulas
Range	Formula
E3:E15	E3	=LET(s,SCAN(,C3:C8,LAMBDA(a,b,a+b)),INDEX(B3:B8,XMATCH(SEQUENCE(SUM(C3:C8)),s,1)))
Dynamic array formulas.

 

[Kra]

Here's another way using dynamic arrays if your version of excel has all of the necessary functions.

Book1
	A	B	C	D	E	F
1
2		List	Repeat			Result
3		Value1	2			Value1
4		Value2	3			Value1
5		Value3	1			Value2
6		Value4	5			Value2
7		Value5	0			Value2
8		Value6	5			Value3
9						Value4
10						Value4
11						Value4
12						Value4
13						Value4
14						Value6
15						Value6
16						Value6
17						Value6
18						Value6
Sheet1

Cell Formulas
Range	Formula
F3:F18	F3	=TRANSPOSE(TEXTSPLIT(CONCAT(REPT(B3:B8&",",C3:C8)),",",,1))
Dynamic array formulas.

Thank you! Does exactly what I need!

 

[Kra]

How about

Fluff.xlsm
	A	B	C	D	E
1
2		List	Repeat
3		Value1	2		Value1
4		Value2	3		Value1
5		Value3	1		Value2
6		Value4	5		Value2
7		Value5	0		Value2
8		Value6	2		Value3
9					Value4
10					Value4
11					Value4
12					Value4
13					Value4
14					Value6
15					Value6
Main

Cell Formulas
Range	Formula
E3:E15	E3	=LET(s,SCAN(,C3:C8,LAMBDA(a,b,a+b)),INDEX(B3:B8,XMATCH(SEQUENCE(SUM(C3:C8)),s,1)))
Dynamic array formulas.

Thank you Fluff! Works perfectly as well!

 

[Fluff]

Glad we could help & thanks for the feedback.