Stack value x times formula

Kra

Board Regular
Joined
Jul 4, 2022
Messages
160
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1
2ListRepeatResult
3Value12Value1
4Value23Value1
5Value31Value2
6Value45Value2
7Value50Value2
8Value62Value3
9Value4
10Value4
11Value4
12Value4
13Value4
14Value6
15Value6
16
17
Sheet1
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Upvote 0
Hi there

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

Book1
ABCDEF
1
2HelperListRepeatResult
31Value12Value1
43Value23Value1
56Value31Value2
67Value45Value2
712Value50Value2
812Value65Value3
9Value4
10Value4
11Value4
12Value4
13Value4
14Value6
15Value6
16Value6
17Value6
18Value6
Sheet1
Cell Formulas
RangeFormula
A4:A8A4=A3+C3
F3:F18F3=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
 
Upvote 0
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
 
Upvote 0
Here's another way using dynamic arrays if your version of excel has all of the necessary functions.

Book1
ABCDEF
1
2ListRepeatResult
3Value12Value1
4Value23Value1
5Value31Value2
6Value45Value2
7Value50Value2
8Value65Value3
9Value4
10Value4
11Value4
12Value4
13Value4
14Value6
15Value6
16Value6
17Value6
18Value6
Sheet1
Cell Formulas
RangeFormula
F3:F18F3=TRANSPOSE(TEXTSPLIT(CONCAT(REPT(B3:B8&",",C3:C8)),",",,1))
Dynamic array formulas.
 
Upvote 0
Solution
How about
Fluff.xlsm
ABCDE
1
2ListRepeat
3Value12Value1
4Value23Value1
5Value31Value2
6Value45Value2
7Value50Value2
8Value62Value3
9Value4
10Value4
11Value4
12Value4
13Value4
14Value6
15Value6
Main
Cell Formulas
RangeFormula
E3:E15E3=LET(s,SCAN(,C3:C8,LAMBDA(a,b,a+b)),INDEX(B3:B8,XMATCH(SEQUENCE(SUM(C3:C8)),s,1)))
Dynamic array formulas.
 
Upvote 0
Here's another way using dynamic arrays if your version of excel has all of the necessary functions.

Book1
ABCDEF
1
2ListRepeatResult
3Value12Value1
4Value23Value1
5Value31Value2
6Value45Value2
7Value50Value2
8Value65Value3
9Value4
10Value4
11Value4
12Value4
13Value4
14Value6
15Value6
16Value6
17Value6
18Value6
Sheet1
Cell Formulas
RangeFormula
F3:F18F3=TRANSPOSE(TEXTSPLIT(CONCAT(REPT(B3:B8&",",C3:C8)),",",,1))
Dynamic array formulas.
Thank you! Does exactly what I need!
 
Upvote 0
How about
Fluff.xlsm
ABCDE
1
2ListRepeat
3Value12Value1
4Value23Value1
5Value31Value2
6Value45Value2
7Value50Value2
8Value62Value3
9Value4
10Value4
11Value4
12Value4
13Value4
14Value6
15Value6
Main
Cell Formulas
RangeFormula
E3:E15E3=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!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top