Row duplication dependent on cell data?

I have a spreadsheet which contains user purchase data for a client. One of the fields provided is quantity. So I end up with something like this:

FName | LName | ... | Quantity

Because nothing can ever be simple, they need an individual row for each item purchased. In other words, if the user purchased 5 of the item, they need the user data repeated 5 times in separate rows. I've been doing this by hand every month, but I've got to believe there's a way to do it programmatically. Sadly, I'm so far behind on my macro/formula programming I can't figure it out. Any suggestions?

edit retag close merge delete

You mean, that if Quantity is 5, you need to insert 4 more rows with identical contents? Should the Quantity be changed to 1 in every cell then? Or it stays 5? What is the reason "they" want such weird data duplication?

( 2019-01-03 20:34:52 +0200 )edit

They do a monthly raffle and draw the winner from a literal hat on Facebook Live. Doesn't matter if the quantity is reset, the end result that column gets deleted before I send them the final list. Basically, since it's a raffle, if you buy five tickets your name needs to be in the hat five times.

( 2019-01-03 20:39:00 +0200 )edit

Sort by » oldest newest most voted

Hello one again @evertiro

I have attached this sample spreadsheet with a little demonstration how it can be done in Calc without programming. The main idea is to define row numbers where the next line from original source shall start, fill column with correct row references and then use this references with INDEX function. Named Ranges are used for better readability. You should extend these ranges as far as your data source needs and you should also expand down formulas as far as needed, rows' count with formulas shall exceed Total rows in result count.

more