Duplicating contents of one Excel sheet column variable times into another.

  • I’d like to paste into one column a set of data e.g.
    F43
    E54
    R22
    F33
    and then enter the number of duplications, e.g. 3, in to a cell which would produce a new column containing for this example,
    F43
    F43
    F43
    E54
    E54
    E54
    R22 etc.
    I’d then copy this new column into Word in order to save as a .dat file to be used to merge print the labels used to store these items.

    Thanks for any help or advise.

  • Following is the way to do it with the formula


    1) Assuming original data begins from cell A1
    2) Enter the number that you want to duplicate in cell C1
    3) Enter following formula in first row in any column
    =INDIRECT("A"&ROUNDUP(ROW(A1)/$C$1,0))
    and then fill down as you want the data


    I hope this will help you


    Jindon

  • making duplicate labels with excel and mailmerge


    Thank you Jindon. That formula works fine. Someday I hope to figure out how :) BTW, it seems one needs to put the formula in the 2nd row in order to have a blank in the top position which the Word data file needs to properly feed the merge. I wonder how one could automate the fill down?


  • Hi, brerigo


    This is how it works: =INDIRECT("A"&ROUNDUP(ROW(A1)/$C$1,0))
    1) Function INDIRECT gives you the value of the cell you named.
    e.g. If you put =INDIRECT("A1") in any cell, you can get the value of the cell A1
    2) ROUNDUP(ROW(A1)/$C$1,0) part is the key.
    ROW(A1) is in substitute for 1, and it increases as you fill down.
    Assume 3 entered in cell C1. It gives you the calculation of ROUNDUP OF 1/3 with no decimal.
    If you put that part of the fomula =ROUNDUP(ROW(A1)/$C$1,0), you can get 1 as a result. So, simply, if you want to start the duplicate data from the second row, put the given formula in the second row and it starts from there.


    3) If the original data has heading in the first row, you need to add 1 in that part
    That is ROUNDUP(ROW(A1)/$C$1,0)+1
    So the formula looks like =INDIRECT("A"&ROUNDUP(ROW(A1)/$C$1,0)+1)


    I hope you can now manage it as you like.


    Jindon

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!