How to make cells repeat themselves a vertain number of times in a when used in labels

I use an Excell sheet to Generate Box/Reel number and Lot No. labels when my shipment arrives. However while production, I need to print multiple labels of the same number (based on the no of pcs that lot has produced) and the print again 'x' number of labels of the next lot.

Currently i enter all the lots i recd in an excell sheet, and mail merge it. (thats ok for labeling the input material)
for the output material I need say ; 10 labels of Lot 1, 15 Labels of lot 2.

Like when I get material my sheet for mail mergo looks like

230 342 13221 426 19.15
231 342 13221 425 18.90
232 342 13221 424 19.90
233 342 13221 423 19.55
234 342 13221 420 22.40
235 342 13221 419 22.05

and when i got for final product labelling it should look like :

230 342 13221 426 19.15
230 342 13221 426 19.15
230 342 13221 426 19.15
230 342 13221 426 19.15
230 342 13221 426 19.15
233 342 13221 423 19.55
233 342 13221 423 19.55
235 342 13221 419 22.05
235 342 13221 419 22.05
235 342 13221 419 22.05
235 342 13221 419 22.05
235 342 13221 419 22.05
235 342 13221 419 22.05

and so on ...

Any way we can do it without having to paste the same cell a number of times ...

Oh, sorry forgot to enter that. Actually the numbers in col B,C & D - combine together to form the lot number. However, we have given a unique Number in Col A which a new number for each lot. Column E is the weight of each lot, and Colomn F is the number of times this lost has to repeat when stickers are mail merged.


A B C D E F
230 342 13221 426 19.15 14
231 342 13221 425 18.90 15
232 342 13221 424 19.90 14
233 342 13221 423 19.55 14
234 342 13221 420 22.40 26
235 342 13221 419 22.05 27

Curently, what I have done is , in a new sheet entered the UniqueNo (col A) and Vlookup'ed up and gotten the values in Col B,C,D &F. Pulled down the unique no, the no of time it is mentioned in coloumn F . and then started mail merge !!

but if there is a simpler easier way !!

Many thanks for such a prompt reply. What does the column H do in the list, where the formula is : =IF(A1="","",F1+H1)

what condition do u fullfill with A1="" ? or what is false in "" ? could u just explain and i an curious to what went behind the scene in the thought process.

Awesum thought process. I guess i will have to create it once to see how it actually works.

In the meantime, I need to add row on the top of the file u created to add relevent coloumn heading When i do that , some rows in the repeated section show them selfs as NA .. and this cant add coloumn header. Could you please redo the same by adding two rows on the top.

Thanks a lot martin ... It made my work super easy, and I have gotten the hang of working with the Index command as well. The index command is pretty much similiar to the Vlook up ? Whats the difference then ?

and one more help, when we repeat the rows, as in the formula made by you, can one coloumn in the repeated rows carry a contineous number (increasing till the number or repeats) that could be suffied with the unique number using concatanate command ? Like how many of one unique number were repeated .. eg. 231/1, 231/2, 231/3, 231/4, 267/1, 267/2, 267/3, 897/1 ..

I am attaching the work sheet i am working on
Can we get what i have put in col M, then col N, is easy to get ? or is there anothr simpler way to get around this ?

Thnaks in advance !!

yeah Count if ..seems easy .. .. i had actually solved it using IF(L2=L1,M1+1,1), to check if current row equals the one above, and add an increamnet to the counter, else reset to 1 !! Attaching sheet here ...

Hi Martin,,

I had put this thread, but no decent replies ..cud u help me on this ?

http://www.excelforum.com/excel-prog...67#post3166367