Forum Discussion

patriciaarnolderg's avatar
patriciaarnolderg
Copper Contributor
Nov 17, 2023

This worked great! how do i get it to repeat?  I have values from A4 to A50
djclements's avatar
djclements
Silver Contributor
Nov 18, 2023

patriciaarnolderg An appropriate VBA solution will depend on which scenario applies to your situation. Please clarify the following:

 

1) Are the number of repeats the same for each item in range A4:A50, based on the number in cell B4?

- OR -

2) Does the range B4:B50 contain a list of numbers indicating a specific number of repeats for each corresponding item in range A4:A50?

 

Incidentally, if you are using MS365, this can also be accomplished with formulas for each scenario as follows:

 

Scenario 1: the number of repeats is set in cell B4

 

=TOCOL(IF(SEQUENCE(, B4), A4:A50))

 

Scenario 2: the number of specific repeats for each individual item is set in range B4:B50

 

=LET(arr, A4:B50,
DROP(REDUCE(0, SEQUENCE(ROWS(arr)), LAMBDA(v,n,
   VSTACK(v, IF(SEQUENCE(INDEX(arr, n, 2)), INDEX(arr, n, 1))))), 1))

 

Note: also include the sheet name in the range references shown above, if you are returning the results to a different worksheet (ie: Sheet1!B4 and Sheet1!A4:A50, or Sheet1!A4:B50).

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Nov 20, 2023

      patriciaarnolderg 

      Sub list()

Dim i, j, k As Long

Range("C:C").Clear

For i = 4 To 50
For j = 1 To Cells(i, 2)

Cells(k + i, 3).Value = Cells(i, 1).Value
k = k + 1

Next j
k = k - 1

Next i

End Sub

      For Scenario 2 described by djclements you can use this macro as well.

Resources