Array works in excel but not in google sheets

[mrjinx007]

Hi,
I have an array that looks at column A-3 through A-245 and displays cells that are not empty. Converted the form to google sheets and although it converts the formula, it does not work.
Any help is greatly appreciated.

 

[MrSyyr]

Hi,
I have an array that looks at column A-3 through A-245 and displays cells that are not empty. Converted the form to google sheets and although it converts the formula, it does not work.
Any help is greatly appreciated.

Do you mean that you have a single formula in a cell that concatenates the non-empty cells in a range?

 

[mrjinx007]

This is what I have in all the cells in the column:

={IF(ROWS($C$3:C3)<=COUNT(A$2:A$237)+COUNTIF(A$2:A$237,"*?"),INDEX(A$2:A$237,SMALL(IF(A$2:A$237<>"",ROW(A$2:A$237)-ROW(A$2)+1),ROWS($C$3:C3))),"")}

This provides me with list of none blank cells

 

[mrjinx007]

It translates into this but gives an error.

=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(ROWS($C$3:C3)<=COUNT(A$2:A$237)+COUNTIF(A$2:A$237,"*?"),INDEX(A$2:A$237,SMALL(IF(A$2:A$237<>"",ROW(A$2:A$237)-ROW(A$2)+1),ROWS($C$3:C3))),"")), 1, 1)

 

[MrSyyr]

It worked for me with no error.
=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(ROWS($C$3:C3)<=COUNT(A$2:A$236)+COUNTIF(A$2:A$236,"*?"),INDEX(A$2:A$236,SMALL(IF(A$2:A$236<>"",ROW(A$2:A$236)-ROW(A$2)+1),ROWS($C$3:C3))),"")), 1, 1)
First I tried a shorter, and different way of limiting the list using iferror and that worked too. If you can get this version to work then that's something.
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX($A$3:$A$23,SMALL(IF($A$3:$A$23<>"",ROW($A$3:$A$23)-ROW($A$3)+1,""),ROW(A3)-ROW($A$3)+1)),"")), 1, 1)

I can only think it was in import error.

 

[Fluff]

I Think this works in Goggle.
=FILTER(A2:A37,A2:A37<>"")

 

[mrjinx007]

It worked for me with no error.
=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(ROWS($C$3:C3)<=COUNT(A$2:A$236)+COUNTIF(A$2:A$236,"*?"),INDEX(A$2:A$236,SMALL(IF(A$2:A$236<>"",ROW(A$2:A$236)-ROW(A$2)+1),ROWS($C$3:C3))),"")), 1, 1)
First I tried a shorter, and different way of limiting the list using iferror and that worked too. If you can get this version to work then that's something.
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX($A$3:$A$23,SMALL(IF($A$3:$A$23<>"",ROW($A$3:$A$23)-ROW($A$3)+1,""),ROW(A3)-ROW($A$3)+1)),"")), 1, 1)

I can only think it was in import error.

You are awesome. Thank you very much.

 

[mrjinx007]

I Think this works in Goggle.
=FILTER(A2:A37,A2:A37<>"")

Thanks for the suggestion. I really didn't want to use the filter if I didn't have to.

 

[Fluff]

I really didn't want to use the filter if I didn't have to.

Why on earth not? It's way faster & more efficient than your formula, it's also far easier to modify if needed.

 

[mrjinx007]

Why on earth not? It's way faster & more efficient than your formula, it's also far easier to modify if needed.

The program is designed to be used by few hundred people who are very low tech and with no knowledge of excel. If would freak them out