Creating comma deliniated list from filtered rows in a table

Copper Contributor



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:






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



Does this return the expected result?



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



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.


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


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




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



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