Creating comma deliniated list from filtered rows in a table

Copper Contributor

Hello

 

I have a table where column 1 is an index number. I then filter this table based on the values in another column.

 

I want to be able to return all the values from the index column into a comma deliniated string. I am using the following to do this:

 

 

=TEXTJOIN(",",TRUE,Table1[Index])

 

 

The problem with this is it returns the values for all rows. Which function can I use to amend it so that it only returns values for the visible filtered rows?

 

Many thanks in advance

7 Replies

@daved2424 

=TEXTJOIN(",",TRUE,FILTER(A2:A13,B2:B13>17))

Does this return the expected result?

textjoin.JPG

 

Unfortunately not as that requires hard-coding the filter value into the formula. I need it to dynamically update as filters are applied.

@daved2424 

=TEXTJOIN(",",TRUE,FILTER(Tabelle2[index],Tabelle2[value]>17))

If you work with a dynamic table you can reference dynamic columns instead of hard-coded ranges.

textjoin filter.JPG

 

Thank you. I appreciate what you are trying to say, however it is the ">17" where I have the problem. You are essentially hardcoding the paramters of the filter into the formula. I want to parameters of the filter to be the rows that are currently visible.

@daved2424 

You are welcome. Unfortunately i didn't understand earlier what you want to do.

=SUBTOTAL(2,[@value])

This is the formula in cell C2 in the example. It returns 1 for all visible rows and 0 for all invisible rows. "1" is used in the TEXTJOIN formula. One doesn't have to change this hard-coded part as long as the index numbers of the visible cells should be returned.

=TEXTJOIN(",",TRUE,FILTER(Tabelle2[index],Tabelle2[Visible rows]=1))

The above formula then returns the expected result.

textjoin all rows.JPG

The screenshot below shows the result of the formula if the rows are filtered. The applied filter in column B is "values above average" in this example and only the index numbers of the visible rows are returned with comma delimiters.

textjoin rows above average.JPG

 

@OliverScheurich 

 

Hmmm ok. My next problem is my values in the values column are text strings so using subtotal would only return zereos.

@daved2424 

=SUBTOTAL(3,[@value])

For text values you can enter this formula in cell C2 in the example.