Make Excel copy rows a certain number of times

**philglobal** · 10-31-2018, 10:45 AM

This question is for the purpose of creating stickers for our inventory- my bosses are insisting on creating exact amounts of stickers rather than in multiples of 10, and currently it is super time consuming.
Is there a way I could have two columns - one containing SKUs, other containing number of rows needed, and have it make a new sheet with that many rows?

As an example, sku column says item_1 , and Number column says 6 , and the result of the function/mmacro would be that a sheet now exists with 6 rows each starting with the SKU item_1?
I could use a vlookup to get the rest of required information in, but if there is a way to fix above problem, it would be a massive time saver!

Thanks so much,
Phil

**Glenn Kennedy** · 10-31-2018, 11:03 AM

Yep.

=IFERROR(INDEX($A$2:$A$4,MATCH(0,INDEX(--(COUNTIF($D$1:D1,$A$2:$A$4)=$B$2:$B$4),0),0)),"")

**philglobal** · 10-31-2018, 11:10 AM

Glenn!
Thanks so much, this is a lifesaver!
God bless,
Phil

**Glenn Kennedy** · 10-31-2018, 11:16 AM

You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

**philglobal** · 10-31-2018, 11:23 AM

will do,
one final question with it!at any given time I may have up to 300-400 skus to reference- I tried changing the formula to have the A and B reference A$2:A$300 and same for Bs,
but that doesn't seem to work - what else do I need to change to make it do that? If possible....

Trying to learn the logic by looking at what you wrote, but I guess I have lots to learn in excel

**Glenn Kennedy** · 10-31-2018, 11:44 AM

COUNTIF($D$1:D1... needs to reference the cell ABOVE the one the formula starts in. What do you mean by "doesn't work"?

**philglobal** · 10-31-2018, 12:01 PM

It seemed like it wasn't working, but now I see it shows as calculating - which makes sense I think- i'm trying to make it produce 1191 rows, which means each time excel is looking back through the previous rows as it goes down.
It just finished and looks like it worked fine, really huge thanks for your help... For future reference would it work faster if I broke it into a few chunks?

Thanks!

God bless!