Rearrange values from Vertical to Horizontal. SIERRA CHART

[mescalante48]

Good day everyone,

how can I rearrange the vertically posted values from column (A) to a horizontal manner, (each one its own column) as the example below?
any similar solution might help. brainstorming is welcome

please consider that no array functions and no VBA solutions are supported by Sierra chart.

see the list of Available functions here:

Spreadsheet Functions - Sierra Chart

Sierra Chart is a professional Trading platform for the financial markets. Supporting Manual, Automated and Simulated Trading.

www.sierrachart.com

again, any idea would help. many thanks!

 

[Jeffrey Mahoney]

test.xlsb
	A	B	C	D	E	F	G	H	I
1		2	3	4	5	6	7	8	9
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
Sheet2

Cell Formulas
Range	Formula
B1:I1	B1	=IF(INDEX($A$2:$A$1000,COLUMN(B1))<>"",INDEX($A$2:$A$1000,COLUMN(B1)),"")

 

[mescalante48]

test.xlsb
	A	B	C	D	E	F	G	H	I
1		2	3	4	5	6	7	8	9
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
Sheet2

Cell Formulas
Range	Formula
B1:I1	B1	=IF(INDEX($A$2:$A$1000,COLUMN(B1))<>"",INDEX($A$2:$A$1000,COLUMN(B1)),"")

when you get a chance please explain why this works

 

[Jeffrey Mahoney]

INDEX needs the array you want to return and the row number within that array. Providing the column number of the cell you in which you created the formula provides the increasing row number to reference within the array. When you copy the formula to the right, the Column it returns is one larger than the last.

 

[Jeffrey Mahoney]

On my end I need to alter the formula so that it looks at the proper row: =IF(INDEX($A$2:$A$1000,COLUMN(B1)-1)<>"",INDEX($A$2:$A$1000,COLUMN(B1)-1),"")

If you had Office 365 you could use the Transpose() function and it would spill the values to the right.