Ask Your Question

Row duplication dependent on cell data?

asked 2019-01-03 20:16:41 +0200

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 flag offensive 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?

SM_Riga gravatar imageSM_Riga ( 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.

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

1 Answer

Sort by » oldest newest most voted

answered 2019-01-05 14:06:51 +0200

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.

edit flag offensive delete link more


Nice! That actually makes sense! Thanks!

evertiro gravatar imageevertiro ( 2019-01-05 15:28:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-03 20:16:41 +0200

Seen: 20 times

Last updated: Jan 05