How to split cell based on value with Macro and without Macro

[scorpio3st]

Example:

I have:

2​	500​
8​	200​
5​	300​
2​	300​
1​	300​
5​	300​
2​	300​
5​	300​
2​	300​

In column A1 is number "2" and in column B1 is "500". I need to do 500/2 and show result in column-multiple rows, C1=250 and C2=250

if I have 500/8 i need to show result in C1,C2,C3,C4,C5,C6,C7,C8.

2​	500​	250​
2​	500​	250​
8​	200​	25​
8​	200​	25​
8​	200​	25​
8​	200​	25​
8​	200​	25​
8​	200​	25​
8​	200​	25​
8​	200​	25​

Thanks in advance.

 

[Peter_SSs]

Here is a formula approach using a helper column (which could be moved &/or hidden)

scorpio3st.xlsm
	A	B	C	D
1	2	500	1	250
2	8	200	3	250
3	5	300	11	25
4	2	300	16	25
5	1	300	18	25
6				25
7				25
8				25
9				25
10				25
11				60
12				60
13				60
14				60
15				60
16				150
17				150
18				300
19
20
Repeat Rows

Cell Formulas
Range	Formula
C2:C9	C2	=IF(A2="","",C1+A1)
D1:D20	D1	=IF(ROWS(D$1:D1)>MAX(C$1:C$100),"",LOOKUP(ROWS(D$1:D1),C$1:C$100,B$1:B$100)/LOOKUP(ROWS(D$1:D1),C$1:C$100,A$1:A$100))

 

[Peter_SSs]

A vba approach

Sub Rpt()
  Dim a As Variant, b As Variant
  Dim dVal As Double
  Dim i As Long, j As Long, k As Long
  
  With Range("A1", Range("B" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To Application.Sum(.Columns(1)), 1 To 1)
    For i = 1 To UBound(a)
      dVal = a(i, 2) / a(i, 1)
      For j = 1 To a(i, 1)
        k = k + 1: b(k, 1) = dVal
      Next j
    Next i
    .Offset(, 2).Resize(k, 1).Value = b
  End With
End Sub

scorpio3st.xlsm
	A	B	C
1	2	500	250
2	8	200	250
3	5	300	25
4	2	300	25
5	1	300	25
6			25
7			25
8			25
9			25
10			25
11			60
12			60
13			60
14			60
15			60
16			150
17			150
18			300
19
Repeat Rows (2)

 

[scorpio3st]

Works perfect. Thanks a lot.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.