Copy Range of cells x times in order

  • hello I have a little problem that needs your help:


    i have variable cells with dates that can be one day up to a full month this way:


    if I choose 2 days


    01-02-18
    02-02-18


    or if I choose 3 days


    01-02-18
    02-02-18
    03-02-18


    and so on up to a full month


    and I have I list of names in another column which I need to repeat the days in front of each name so I get this result:


    if 3 days


    sample name 1 01-02-18
    sample name 1 02-02-18
    sample name 1 03-02-18
    sample name 2 01-02-18
    sample name 2 02-02-18
    sample name 2 03-02-18


    if 2 days


    sample name 1 01-02-18
    sample name 1 02-02-18
    sample name 2 01-02-18
    sample name 2 02-02-18


    so my question is how to repeat dates in order as explained above in front of each name

  • You could try these formulas...


    Assuming your dates are in column A starting at A1, and your names are in column C starting at C1, then say in F1 enter:


    =IF(ROWS(F$1:F1)>COUNTA(A:A)*COUNTA(C:C),"",INDEX(C:C,MOD(INT((ROW()-ROW($F$1))/COUNTA(A:A)),COUNTA(C:C))+1))


    copied down as far as you desire.


    and in G1 enter:


    =IF(F1="","",INDEX(A:A,MOD(ROW()-ROW(G$1),COUNTA(A:A))+1))


    copied down


    adjust ranges to suit.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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