Results 1 to 4 of 4

Thread: Auto fill a range of cells in a row based on the value in another cell

  1. #1

    Post Auto fill a range of cells in a row based on the value in another cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi There,

    I am new to this forum and would post this hoping someone could help me. Please see the table below.
    A B C D E F G H I J K L M N
    2 5 1 1 1 2 2 2 2 2 3
    3 2 4 1 1 1 2 2 3 3 3 3
    1 6 3 1 2 2 2 2 2 2 3 3 3

    I have an excel worksheet (excel 2010) with numbers in Columns A,B & C. What I am trying to acheive is to automatically put 1s, 2s and 3s based on the values in A,B & C respectively into columns starting from E.
    For example, I have 2 in A1, 5 in B1 and 1 in C1. In this case, I need to put 1 two times and then 2 five times and then 3 one time starting from column E as shown in the table above.

    Can anyone help me with a formula or a vb script to do this?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    You can do this with formulas, theoretically....

    You will need some helper columns though.

    So, try this.

    Assuming the data starts in A1:C1, then in D1 enter formula:

    =SUM($A1:A1)

    copied across 3 columns, then down as far as needed.

    You can hide these columns, if desired.

    Then in H1 enter formula:

    =IF(COLUMNS($H1:H1)>$F1,"",INDEX({1;2;3},MIN(IF(COLUMNS($H1:H1)<=$D1:$F1,COLUMN($D1:$F1)-COLUMN($D1)+1))))

    confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied across as far as you think you will ever need to get the max repeats. (try not to go to much over).

    then copy down these down as far as columns A:C.
    Attached Files Attached Files
    Last edited by NBVC; 2014-12-11 at 09:01 PM.


  3. #3
    Thanks a lot NBVC,

    It worked perfect !!
    However, I have another question. Is there a way to start putting the 1s in different columns for each rows, say for row one 1s start at H, for row 2 it starts at K etc.
    Consider column A,B & C have the number of repetitions for 1,2 & 3 respectively.
    Column D,E & F has the hidden formulae
    Column G has the number reference as to where the 1 s should start (Numerical value like 10)
    First row should start from H

    Any idea?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Ok, in H1 enter formula:

    =IF(COLUMNS($H1:H1)-$G1+1>$F1,"",IF(COLUMNS($H1:H1)-$G1+1>=1,INDEX({1;2;3},MIN(IF(COLUMNS($H1:H1)-$G1+1<=$D1:$F1,COLUMN($D1:$F1)-COLUMN($D1)+1))),""))

    confirmed with CTRL+SHIFT+ENTER not just ENTER and copy across as far as necessary and down as far as necessary.

    Does that work for you?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •