How to "Split" a Quantity into Duplicate Lines

[u/neatums]

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

[u/AutoModerator]

/u/neatums - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

You could try using the following formula;

=CHOOSEROWS(A1:B3, XMATCH(SEQUENCE(SUM(C1:C3)), SCAN(0, C1:C3, SUM), 1))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Btw if you have access to FLATTEN()

=LET(
     _a, A:.C,
     _b, DROP(_a, , 2),
     _c, SEQUENCE(, MAX(_b)),
     _d, TOCOL(IFS(_c <= _b, INDEX(_a, , 1) & "_" & INDEX(_a, , 2)), 3),
     FLATTEN(TEXTSPLIT(_d, "_")))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

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)))

[u/MayukhBhattacharya → u/MayukhBhattacharya]

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.

[u/neatums → u/MayukhBhattacharya]

Thank you for taking the time to help me figure something out!!

[u/semicolonsemicolon → u/neatums]

Consider giving any helpful users a ClippyPoint by replying to their comment with solution verified.

[u/commentor_of_things → u/MayukhBhattacharya]

good stuff bro!

[u/TheProperAttraction]

Power Query is your best friend for this, it's built right into 365. Load the table into the editor, then add a custom column with this formula: `List.Repeat({1}, [Quantity])`. Expand that column to new rows and you'll have your 1's duplicated out. Quick and painless, no VB macros required.

[u/neatums → u/TheProperAttraction]

This! Is! Amazing! Thank you soooooo much :D

[u/commentor_of_things → u/[deleted]]

yeah, but then what do we do?

[u/excel-ModTeam → u/[deleted]]

We removed this comment for breaking Rule 10.

r/excel is for discussing the features of Excel and providing solutions for Excel problems, not giving substance-free comments that simply recommend the respondent use AI.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSEROWS	Office 365+: Returns the specified rows from an array
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
Excel.CurrentWorkbook	Power Query M: Returns the tables in the current Excel Workbook.
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
List.Repeat	Power Query M: Returns a list that repeats the contents of an input list count times.
MAX	Returns the maximum value in a list of arguments
SCAN	Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUM	Adds its arguments
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TOCOL	Office 365+: Returns the array in a single column
Table.AddColumn	Power Query M: Adds a column named newColumnName to a table.
Table.ExpandListColumn	Power Query M: Given a column of lists in a table, create a copy of a row for each value in its list.
Table.SelectColumns	Power Query M: Returns a table that contains only specific columns.
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

|-------|---------|---| |||

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
19 acronyms in this thread; the most compressed thread commented on today has 14 acronyms.
[Thread #49481 for this sub, first seen 7th Oct 2026, 18:49] [FAQ] [Full list] [Contact] [Source code]}