Explode a list based on a quantity

[cwolfe8229]

I need some help from the experts please. I have a list of materials, which varies in length by day. I need to explode this list into individual line items that repeat based on Qty, kind of like a reverse Pivot Table. This list is typically hundreds of line items per day. For example:

[TABLE="width: 137"]
<tbody>[TR]
[TD]Material[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]XYZ1[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

needs to become:

[TABLE="width: 137"]
<tbody>[TR]
[TD]Material[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]ABC1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]XYZ1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]XYZ1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]XYZ1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions?
Thanks

 

[Joe4]

Here is some VBA code that should do what you want:

Sub ExplodeList()

    Dim lr As Long
    Dim r As Long
    Dim ct As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows going backwards
    For r = lr To 2 Step -1
'       Get count of rows needed
        ct = Cells(r, "B")
'       Insert needed rows
        If ct > 1 Then
            Rows(r + 1 & ":" & r + ct - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range(Cells(r + 1, "A"), Cells(r + ct - 1, "A")) = Cells(r, "A")
            Range(Cells(r, "B"), Cells(r + ct - 1, "B")) = 1
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

Note that this code assumes:
- Data is in columns A and B
- Header row in row 1
- Data starts on row 2

Code may need to be amended slightly if those conditions don't match exactly.

 

[sandy666]

maybe

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Material[/td][td=bgcolor:#5B9BD5]Qty[/td][td][/td][td=bgcolor:#70AD47]Material[/td][td=bgcolor:#70AD47]Qty[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC1[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]ABC1[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XYZ1[/td][td]

3​

[/td][td][/td][td]ABC1[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]XYZ1[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]XYZ1[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]XYZ1[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = Table.AddColumn(Source, "Custom", each {1..[Qty]}),
    Expand = Table.ExpandListColumn(List, "Custom"),
    RC = Table.RemoveColumns(Expand,{"Custom"})
in
    RC[/SIZE]

 

[cwolfe8229]

Joe4, Thank you, that worked and I was able to edit the code to add columns of additional data.

sandy444, Thank you too, but I am a complete novice in VBA and macros and your solution looks cleaner, but I couldn't figure out how to get it to work.

 

[Joe4]

You are welcome.

Note that Sandy's response uses Power Query. Also, I think the Qty field would need some adjustment, as it is returning the original amount instead of the 1s you wanted on each line,

 

[sandy666]

sandy444, Thank you too, but I am a complete novice in VBA and macros and your solution looks cleaner, but I couldn't figure out how to get it to work.

sandy666

and this is Power Query M-code, not vba

edit:
Thanks Joe

 

[Eric W]

Just for completeness, here's a formula method:

	A	B	C	D	E
Material	Qty	Material	Qty
ABC1	ABC1
XYZ1	ABC1
QQQ	XYZ1
XYZ1
XYZ1
QQQ
QQQ
QQQ
QQQ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(D2="","",1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$5,MATCH(ROWS($D$2:$D2)-1,SUBTOTAL(9,OFFSET($B$1,0,0,ROW($B$2:$B$5)-ROW($B$2)+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]



Although to be honest, I think either of the previous solutions would be better.