Remove all Zeros from a column, no array, no VBA

[mescalante48]

Hello everyone,

please see below, can we remove all zeros from one column and take only the nonzeros values to a different column?

looking for a formula to do this, no arrays, please.

 

[Fluff]

How about

=Index(a$1:a$11,aggregate(15,6,(row(a$1:a$11)-row(a$1)+1)/(a$1:a$11<>0),rows(b$1:B1)))

 

[mescalante48]

How about

=Index(a$1:a$11,aggregate(15,6,(row(a$1:a$11)-row(a$1)+1)/(a$1:a$11<>0),rows(b$1:B1)))

I'm afraid AGGREGATE is not supported :/

please see below for all supported functions.

Spreadsheet Functions - Sierra Chart

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

www.sierrachart.com

 

[Joe4]

I'm afraid AGGREGATE is not supported :/

please see below for all supported functions.

Spreadsheet Functions - Sierra Chart

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

www.sierrachart.com

Please be sure to mention that you using Sierra Charts in every question you ask on it!
It is best to include it right in the title too.

 

[Fluff]

As this is for Sierra charts, I cannot help as I know nothing about it.

 

[mescalante48]

Please be sure to mention that you using Sierra Charts in every question you ask on it!
It is best to include it right in the title too.

understood, Sorry

 

[mescalante48]

As this is for Sierra charts, I cannot help as I know nothing about it.

ok, do you know about any other solution that doesn't use AGGREGATE?

SC and EXcel are almost the same, just a small portion of functions are not supported. that and no array and no VBA.

 

[Joe4]

understood, Sorry

Thank you.

Just remember that this is an Excel (and Microsoft Access) Help Forum.
While Sierra Charts (and Google Sheets) may be similar to Excel in many ways, they are not the same.
So if you are using anything other than Excel (or Access), you need to let us know. Otherwise, you will get Excel answers (which might not work for you, as you have discovered).

 

[Fluff]

understood, Sorry

Have you really? Sum of values with irregular row intervals. for Sierra Charts

 

[Sufiyan97]

Try

Book1
	B	C	D	E
1	223		3456
2	0		685
3	0		223
4	0		75
5	3456
6	0
7	0
8	685
9	0
10	0
11	75
12
13
Sheet1

Cell Formulas
Range	Formula
D1:D11	D1	=IF(LARGE($B$1:$B$11,ROW())=0,"",LARGE($B$1:$B$11,ROW()))