Logical array multiplication

[Zage12]

I have a table like so

Some-Column
laurel
hardy
cheynny
cherry
(more stuff)

My goal is to return an array containing indices of all non-empty cells, in this case:
{1, 2, 4, 6,...}

I thought I could simply multiply the result of not(isblank({<range1>})) by row({<range1>}), i.e. not(isblank(A2:A15))*row(A2:A15)
In said array, the first result is a numeric index, however every index from 2 to n is a boolean.

Result
2
TRUE
FALSE
TRUE
FALSE
TRUE

How can I get the 2:n elements of the array to convert to numeric indices?

(If curious, I’m trying to return a list of non-empty cells for use in a dropbox menu via Data->Validity. I want it to list all values some user enters in another column. Said column can grow arbitrarily large. First step is doing this!)

[Lupp]

@Zage12

I thought I could simply multiply the result of not(isblank({<range1>})) by row({<range1>}), i.e. not(isblank(A2:A15))*row(A2:A15)

What’s your concept of multiplying two one-column-arrays? Surely componentwise?
Well this will work, but the “format” of the result won’t show empty/nothing for blank/empty cells, but 0 - and, of course, it will lock as many cells for the output as the original columns had rows.

@Zage12

{1, 2, 4, 6,…}

This looks like a “constant array”. If you get a result this way, how would you use it?
If you want to get the non-empty rows as a list (best semicolon-separated, use

=TEXTJOIN(";";1;IF(ISTEXT($A$2:$A$1001)*($A$2:$A$1001<>"");ROW($A2:$A1001) - ROW($A$2)+1;""))  

under array-evaluation.

Next time please attach a usable example as .ods file. I’m tired of creating examples for testing. That’s the questioners’ job.

nonEmptyCellsListByIndex.ods (13.2 KB)

[Zage12]

@Lupp

Next time please attach a usable example as .ods file. I’m tired of creating examples for testing. That’s the questioners’ job.

Considering this is my first post here, thank you for kindly letting me know the need for an example file.
Array_example.ods (13.1 KB)

I hope it illustrates the reason for my post:
I have the same formula, entered 5 separate times, =NOT(ISBLANK(A2:A14))*ROW(A2:A14).

• Free_Form_Text, it produces an array of TRUE, FALSE on Attempt 1; it produces a numerical index on Attempt 5.
• Dropdown, Same text, with a dropdown menu. Attempt 3 and Attempt 4 produce same results as Attempt 1 and Attempt 5.
• Spreadsheet_Sample contains the actual spreadsheet I’m working with. Real Attempt contains the same formula (different indices: A2:A8), with different output (index 2 followed by TRUE/FALSE).
  N.B This was copied from an .xlsx file (collaborators).

I was primarily asking this question due to the…markedly different results I was getting using the same formula, and was hoping for insight into that matter.

@Lupp

This looks like a “constant array”. If you get a result this way, how would you use it?

As I…mentioned, my ultimate goal is to provide a dropdown menu that lists all non-empty values from a separate column. E.g. If column A was {'A', , 'B',}, then the dropdown menu in column B would display A and B. If a collaborator adds another row (e.g. new test they’d like to perform), e.g. {'A', , 'B', 'C'} , then the dropdown menu in column B would display A, B, and C, etc. Why this way?: It’s a way for them to reference back an optional parameter from a previous entry, I guess.

Doing that “constant array” is just the first step in doing so.

[Villeroy]

@Zage12

My goal is to return an array containing indices of all non-empty cells,

You have a database component at hand where recordset operations are trivial.
On sheet, you can query non-empty record sets by means of the standard filter dialog. Use “not empty” together with option “copy output”.
When mis-using spreadsheets as poor man’s database surrogate, you are constantly maintaining references, copying and expanding formulas, adjust formattings anyway.

[Lupp]

@Zage12

My goal is to return an array containing indices of all non-empty cells, in this case:
{1, 2, 4, 6,...}

@Zage12

How can I get the 2:n elements of the array to convert to numeric indices?

(where I don’t understand the “2:n elements”)

@Zage12

As I…mentioned, my ultimate goal is to provide a dropdown menu that lists all non-empty values from a separate column.

(emphasis by Lupp; would assume the mention of "dropdown was appended to the question by editing)

I still can’t coalesce this. I also failed to understand the example.

Concerning the dropdopwn idea: The >Data>Validity tool is made for such a purpose. There you can simply exclude empty cells in the cell-range-mode.

Probably also the suggestion by @Villeroy in his comment on the questipon here may help you.

If you are bewildered just by the Boolean number format in an example: I don’t know for what reasons it probably was created automatically. Anyway you can simply change it via
>Format Cells...>>(Tab) Numbers (“0” in place of “General”).
You can also try =ROW(A2:A10)*NOT(ISBLANK(A2:A10)). The first factor may be responsible for the automatic formatting.

[Villeroy]

Download 2 files to the same directory:
SheetDB.odb (2.6 KB)
SheetDB.ods (13.1 KB)
and register the odb file : Tools>Options>Base>Databases [Add…]
Open the spreadsheet file only.
Source sheet contains a database range “Items_DBRange” and arbitrary other content.
Target sheet has a linked database range “Import1” with the filtered and sorted items from “Items_DBRange”.

1. Insert a new cell anywhere in “Items_DBRange” and enter a new name.
2. Save the spreadsheet file
3. Click any cell in “Import1” and call Data>Refresh.

P.S. It is possible to filter out any duplicates by adding the word DISTINCT to the database query.