cell function to copy column column cells to multiple rows on another column

[Claudius]

Hi,
I am a newbie at Excel. I am trying to automate an excel sheet that does the following. The user enters a list of tickers in a specific column. I need to pick up that column with tickers and replicate each ticker 3 times into another column
For ex. input on sheet1

Sheet1
Column1
t1
t2
t3
t4

My output on sheet2 should read like this. Basically each ticker has to be replicated 3 times, as there are 3 strategies for each ticker, and multiple formulae that spin out of each ticker item in each row. Normally I need to manually copy paste each newly input ticker 3 times on the new sheet. Then hit Shift F9 and all the calculations on the sheet get kicked off. I was wondering if the user enters the ticker on sheet1, and after hitting shift F9 on sheet2, the tickers get automatically populated on sheet2, and the corresponding formulae get computed as well

Sheet2
Column1
t1
t1
t1
t2
t2
t2
t3
t3
t3
t4
t4
t4

Thank you very much for any assistance

 

[T. Valko]

Hi,
I am a newbie at Excel. I am trying to automate an excel sheet that does the following. The user enters a list of tickers in a specific column. I need to pick up that column with tickers and replicate each ticker 3 times into another column
For ex. input on sheet1

Sheet1
Column1
t1
t2
t3
t4

My output on sheet2 should read like this. Basically each ticker has to be replicated 3 times, as there are 3 strategies for each ticker, and multiple formulae that spin out of each ticker item in each row. Normally I need to manually copy paste each newly input ticker 3 times on the new sheet. Then hit Shift F9 and all the calculations on the sheet get kicked off. I was wondering if the user enters the ticker on sheet1, and after hitting shift F9 on sheet2, the tickers get automatically populated on sheet2, and the corresponding formulae get computed as well

Sheet2
Column1
t1
t1
t1
t2
t2
t2
t3
t3
t3
t4
t4
t4

Thank you very much for any assistance

With your data on Sheet1 in the range A2:A5...

Assume you want the repeated list on Sheet2 starting in cell A2...

Enter this formula in A1. This will return the number of rows needed to make the list.

=ROWS(Sheet1!A2:A5)*3

Enter this formula in A2 and copy down until you get blanks:

=IF(ROWS(A$2:A2)>A$1,"",INDEX(Sheet1!A$2:A$5,CEILING(ROWS(A$2:A2)/3,1)))

 

[Claudius]

Hello Biff,
Thank you for your quick reply, this works perfect!! I also created a dynamic range on sheet1, and added that in the INDEX function

Thanks!
Claudius

 

[Claudius]

Hello,

I am having another related issue with respect to using a dynamic range to capture the A2 to A5 reference on sheet1. I thought I nailed this down but am stuck with a small issue. I used the
TList=OFFSET($A$2,0,0,MATCH("*",$A:$A,-1),1)
function ( googled this one) to get a dynamic range variable. But I am having an issue with the height attribute(MATCH("*",$A:$A,-1),) of this function. I would like to have the height of the column start from A2 and get down to the last inputted value. Any thoughts on how I can do this please? I then plan to apply the ROW followed by INDEX function to the TList Range

Thanks,
Claudius

 

[T. Valko]

Hello,

I am having another related issue with respect to using a dynamic range to capture the A2 to A5 reference on sheet1. I thought I nailed this down but am stuck with a small issue. I used the
TList=OFFSET($A$2,0,0,MATCH("*",$A:$A,-1),1)
function ( googled this one) to get a dynamic range variable. But I am having an issue with the height attribute(MATCH("*",$A:$A,-1),) of this function. I would like to have the height of the column start from A2 and get down to the last inputted value. Any thoughts on how I can do this please? I then plan to apply the ROW followed by INDEX function to the TList Range

Thanks,
Claudius

Try it like this...

TList: =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$1000,MATCH("zzzzz",Sheet1!$A$2:$A$1000))

Adjust for a reasonable end of range. I used A1000.

Then the formulas become:

Sheet2 A1: =ROWS(TList)*3

Sheet2 A2 copied down:

=IF(ROWS(A$2:A2)>A$1,"",INDEX(TList,CEILING(ROWS(A$2:A2)/3,1)))

Or, you could combine the 2 and eliminate the formula entered in A1.

Sheet2 A2 copied down:

=IF(ROWS(A$2:A2)>ROWS(TList)*3,"",INDEX(TList,CEILING(ROWS(A$2:A2)/3,1)))

Or, you could create another defined name for ROWS(TList)*3.

RCount: =ROWS(TList)*3

Then:

=IF(ROWS(A$2:A2)>RCount,"",INDEX(TList,CEILING(ROWS(A$2:A2)/3,1)))

 

[Claudius]

Thanks,
Great options.
so we have to adjust for a reasonable end of range, there is no way to specify a dynamic end of range?

Thanks,

 

[T. Valko]

Thanks,
Great options.
so we have to adjust for a reasonable end of range, there is no way to specify a dynamic end of range?

Thanks,

You can use up to the last row of whichever version of Excel you're using.

A2:A65536
A2:A1048576

The "dynamic" end of range is being calculated from a larger range. I'm just used to not using more range size than is needed.

For example, if I know for certain that this wll never use more than 1000 rows then I'll use A1000 as the end of range.

 

[Claudius]

Ah, makes sense now..
Thank you..

 

[T. Valko]

Ah, makes sense now..
Thank you..

You're welcome. Thanks for the feedback!