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

1. ## 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

2. ## 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]

3. ## 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. ## 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. ## 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. ## Re: Fill a variable number of cells in a column based upon a variable in another column

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

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

Originally Posted by VinoTinto
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