New list with repeated entries

[Daniel N]

Hello,

What I wish to do is the following:

Based on these two lists:

[TABLE="width: 100"]
<tbody>[TR]
[TD]Entry1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Entry2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Entry3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Create the following list:

[TABLE="width: 100"]
<tbody>[TR]
[TD]Entry1[/TD]
[/TR]
[TR]
[TD]Entry2[/TD]
[/TR]
[TR]
[TD]Entry2[/TD]
[/TR]
[TR]
[TD]Entry2[/TD]
[/TR]
</tbody>[/TABLE]

In words that is: To make a list which repetes the content of column 1 as many times as specified by column 2.

I tried searching and writing such a function on my own but quickly ran into problems.

Can someone assist with an easy solution?

Thanks,
Daniel

 

[Eric W]

Welcome to the forum.

If you want a formula solution, try:

	A	B	C	D
List	Count	New List
Entry1	Entry1
Entry2	Entry2
Entry3	Entry2
Entry4	Entry2
Entry5	Entry4
Entry4
Entry5

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ROWS($D$2:$D2)-1< SUBTOTAL(9,OFFSET($B$2,0,0,ROW($B$2:$B$10)-ROW($B$2)+1)),ROW($B$2:$B$10)-ROW($B$2)+1),1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




Put the formula in D2, change the references to match your sheet, confirm with Control+Shift+Enter. Drag it down the column as far as needed.

You could also do this with a VBA routine.

Let us know if this helps.

 

[Daniel N]

This solution worked perfectly! Thanks alot for the help!

If it's not too much trouble it would be quite interesting and educational to see the VBA routine as well.

/Daniel

 

[Eric W]

There are a lot of ways to do this with VBA, here's one.

Press Alt-F11 to open the VBA editor, from the menu, click Insert > Module, and paste this into the window that opens:

Sub MakeList()
Dim InData As Variant, OutData() As Variant
Dim InLoc As String, OutLoc As String, LastRow As Long, i As Long, x As Long, TotRows As Long

    InLoc = "A1"
    OutLoc = "D1"
    
    LastRow = Cells(Rows.Count, Left(InLoc, 1)).End(xlUp).Row
    InData = Range(InLoc).Resize(LastRow, 2)
    TotRows = WorksheetFunction.Sum(Range(InLoc).Offset(0, 1).Resize(LastRow, 1))
    ReDim OutData(1 To TotRows + 1, 1 To 1)
    OutData(1, 1) = "New List"
    
    x = 2
    For i = 2 To UBound(InData)
        For j = 1 To InData(i, 2)
            OutData(x, 1) = InData(i, 1)
            x = x + 1
        Next j
    Next i
    
    Columns(Left(OutLoc, 1) & ":" & Left(OutLoc, 1)).ClearContents
    Range(OutLoc & ":" & Left(OutLoc, 1) & TotRows + 1) = OutData
    
End Sub

Change the cells in red to match your sheet. Must be row 1, and headings expected. Press F5 to run the code. Or, go back to Excel, press Alt-F8 and choose MakeList and Run it.

Glad to help!