+ Reply to Thread
Results 1 to 7 of 7

Fill a variable number of cells in a column based upon a variable in another column

  1. #1
    Registered User
    Join Date
    05-21-2020
    Location
    Wale
    MS-Off Ver
    365
    Posts
    4

    Fill a variable number of cells in a column based upon a variable in another column

    I am trying to figure out how to do the following. I deal with a number of records over 24 hours, split into hourly episodes. For example (please excuse the pipe, that is to indicate the new column along):

    Hour |Episodes
    0 | 3
    1 | 2
    2 | 6


    What I need to do is to create a new column which has hour 0 filled down 3 times (for each of 3 episodes), Hour 1 likewise filled down 2 times, for 2 episodes,; hour 2 filled down 6 times for 6 episodes. So the resulting column would be:
    0
    0
    0
    1
    1
    2
    2
    2
    2
    2
    2

    I'll be dealing with many hundreds of records more than this but hopefully this gives an idea
    Last edited by VinoTinto; 09-30-2020 at 06:41 AM.

  2. #2
    Forum Contributor
    Join Date
    03-18-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    241

    Re: Fill a variable number of cells in a column based upon a variable in another column

    Hi,

    See my attachment

    A6
    =INDEX($A$2:$A$4, MATCH(FALSE, COUNTIF($A$5:A5, $A$2:$A$4)=$B$2:$B$4, 0))
    and copy down

    This is array [CTRL+SHIFT+ENTER]
    Attached Files Attached Files
    Last edited by bluesky63; 09-30-2020 at 07:00 AM.
    Christopher Yap

  3. #3
    Registered User
    Join Date
    05-21-2020
    Location
    Wale
    MS-Off Ver
    365
    Posts
    4

    Re: Fill a variable number of cells in a column based upon a variable in another column

    Thank you so much, you are a star !!

  4. #4
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: Fill a variable number of cells in a column based upon a variable in another column

    Array formula

    HTML Code: 
    =IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$4>=COLUMN($A:$AZ),ROW($2:$4),4^8),ROW(A1))),"")

  5. #5
    Registered User
    Join Date
    05-21-2020
    Location
    Wale
    MS-Off Ver
    365
    Posts
    4

    Re: Fill a variable number of cells in a column based upon a variable in another column

    Hi wk9128 - this only seems to work down to 52 rows for each hour. I am interested in how you have done this but is there a limit on what Excel can do - sometimes I will have hundreds of episodes each hour. Thanks

  6. #6
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,242

    Re: Fill a variable number of cells in a column based upon a variable in another column

    Please try
    =FILTERXML("<n>"&CONCAT(REPT("<m>"&A2:A4&"</m>",B2:B4))&"</n>","//m")

  7. #7
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: Fill a variable number of cells in a column based upon a variable in another column

    Quote Originally Posted by VinoTinto View Post
    Hi wk9128 - this only seems to work down to 52 rows for each hour. I am interested in how you have done this but is there a limit on what Excel can do - sometimes I will have hundreds of episodes each hour. Thanks
    You're Welcome. Thank You for the feedback

    formula **** COLUMN($A:$AZ) *** pls change to COLUMN($A:$IV) or XFD ,Consider the Computer CPU processing speed of your computer
    Last edited by wk9128; 10-01-2020 at 01:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 03-10-2017, 02:20 AM
  2. How to fill a column with data if the column has a variable length?
    By flyingdutch06 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2016, 09:02 AM
  3. [SOLVED] I want to create a variable based on the number of Y's in a column
    By Jubb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2014, 08:33 PM
  4. [SOLVED] Sum column based on variable start and variable end months
    By Steve N. in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-06-2013, 08:01 PM
  5. [SOLVED] checking a variable number of cells in a column
    By Thatos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-29-2013, 05:41 AM
  6. Replies: 6
    Last Post: 04-14-2012, 04:07 AM
  7. Sum cells based on a row variable and seperate column variable
    By CheeseHeadTransplant in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-23-2005, 02:05 PM

Bookmarks

Posting Permissions

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