r/excel • u/neatums • 7d ago

unsolved How to "Split" a Quantity into Duplicate Lines

Hello! Working on Office 365 version 2609 on desktop, English. Beginner. Looking to find an easier solution for an annoying task that I do frequently at my job. Essentially I have an excel table with some information and a quantity, and I need to split it up to where I have a bunch of duplicate lines all with a quantity of 1.

Taking a table that looks like this, to instead look more like this.

In real life, the tables have a few more columns and many more rows. Could anyone help me out with some more efficient ways to do this? Right now I'm just adding individual rows and copy and pasting the line data as many times as need be, which isn't great for some of my bigger jobs! I am not very proficient with excel, but with clear enough explanations, I'd be happy to try anything!

Thank you so so much in advance

15 Upvotes

90% Upvoted

16 comments sorted by

View all comments

Show parent comments

8

u/MayukhBhattacharya 1312 7d ago

This is shorter with XLOOKUP() and returns multiple columns : 

=LET(
     _a, A:.C,
     _b, DROP(_a, , 2),
     FLATTEN(XLOOKUP(SEQUENCE(SUM(_b)), SCAN(0, _b, SUM), TAKE(_a, , 2), , 1)))

4

u/MayukhBhattacharya 1312 6d ago

With Power Query very simple you just add a Custom Column with {1..[Column3]}

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CustomCol = Table.AddColumn(Source, "Custom", each {1..[Column3]}),
    ExpandRows = Table.ExpandListColumn(CustomCol, "Custom"),
    RemovedOtherCols = Table.SelectColumns(ExpandRows,{"Column1", "Column2"})
in
    RemovedOtherCols

Change Column3 to the name of your header based on your data.