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:

Material	Qty
ABC1	2
XYZ1	3

<tbody>
</tbody>

needs to become:

Material	Qty
ABC1	1
ABC1	1
XYZ1	1
XYZ1	1
XYZ1	1

<tbody>
</tbody>

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

Material	Qty		Material	Qty
ABC1	2​		ABC1	2​
XYZ1	3​		ABC1	2​
			XYZ1	3​
			XYZ1	3​
			XYZ1	3​

[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
1	Material	Qty		Material	Qty
2	ABC1	2		ABC1	1
3	XYZ1	3		ABC1	1
4	QQQ	4		XYZ1	1
5				XYZ1	1
6				XYZ1	1
7				QQQ	1
8				QQQ	1
9				QQQ	1
10				QQQ	1
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =IF(D2="","",1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=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))))&""} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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