Is it possible to concatenate a range of cells based on a condition is Numbers?

MS Excel formula example: (from linked page in post above):

Numbers supports only a few specific functions involving arrays. In other cases, you'll need to build the array outside the function, as is done in this example.

The result is found in the cell D12:

Column C formula, entered in C2 and filled down: B2+ROW()×0.00000001

Cell A12 contains a single space.

Regards,

Barry

TEXTJOIN would be a nice function to have but it still wouldn't get the job done. The formula is an Excel "array" formula that is doing an entire column's worth of processing within the one cell.

Here is another way to do it in Numbers where all the work is done in the second table. The negative is it needs as many columns as you have rows in the table of letters.

As you can see, the second table is only showing rows A and P. All the rows between are hidden.

B2 =IF(INDEX(Table 1::$C,COLUMN())=$A2,INDEX(Table 1::$B,COLUMN()),"")

Fill down to complete the column

C2 =B2&IF(INDEX(Table 1::$C,COLUMN())=$A2,INDEX(Table 1::$B,COLUMN()),"")

Fill to the right all the way to the last column in your table (column P for mine)

Fill down to complete all those columns.

Hide all but A and the rightmost column.

If, instead, you choose to have it do the work in the table of letters (as shown in the other posts), I would insert new columns into that table headed by the numbers 1, 2, 3, etc and have each of those columns concatenate its letters. The final row would be the results for all of the numbers. A simple function using INDEX in the second table would get the result from the correct column.

Alternatively, the column headers for those columns could come from your other table. I'll post an example.

This is an example with the new columns with the numbers hard-coded into the header cells.

Table 1 D1 and to the right are numbers, not formulas

Table 1 D2 =IF($C2=D$1,$B2,"")

Table 1 D3 =D2&IF($C3=D$1,$B3,"")

Fill both formulas to the rightmost column. Fill down with the formula in row 3.

Table 2 column A are numbers you choose

Table 2 B2 =INDEX(Table 1::A:H,ROWS(Table 1::D),3+$A2)&""

Fill down to complete the column.

Or,

The formula in C3, filled down:

=IF($B3=B$1,C2&$A3,IF(C2="","",C2))

SG