I want to be able to copy/duplicate several columns of data based on their qty in another column.
I have my data in col. A- C & the qty in D. Basically it is "A-C" times D.

And on the right, that is how I imagine the result to be.

enter image description here

Here's a sample spreadsheet: https://docs.google.com/spreadsheets/d/1W8L445SfSEIrrPgwPaPryC72DK_zwvyCbU6ot1ezsYM/edit#gid=707375238

  • Inserting redundant copies of data rows may not be the best strategy to manage orders that specify multiple copies of an item. Is this an XY problem? Commented Aug 15, 2021 at 6:09
  • Hi @doubleunary this is not for record keeping, this is to create barcode labels. So each individual package (book) would have a label. In cases there is more than 1 nos, then I want to be able to create more labels without having to manually duplicate the rows. And btw, I know excel/sheets has some way of creating barcodes, but we use a third party for this. And thank you for your solution, I haven't checked it. But I didn't want you thinking this was an XY so I replied to this first. :)
    – Genny
    Commented Aug 15, 2021 at 12:08

1 Answer 1


Use query(flatten()) and vlookup() like this:

=arrayformula( query( flatten( if( sequence(1, max(D2:D)) <= D2:D, A2:A, iferror(1/0) ) ), "where Col1 is not null", 0 ) )

=arrayformula( iferror( vlookup(K2:K, A2:D, column(B2:D), false) ) )

See your sample spreadsheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.