Creating comma deliniated list from filtered rows in a table

Question

Hello&nbsp;I have a table where column 1 is an index number. I then filter this table based on the values in another column.&nbsp;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:&nbsp;&nbsp;=TEXTJOIN(",",TRUE,Table1[Index])&nbsp;&nbsp;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?&nbsp;Many thanks in advance

oliverscheurich · Answer

daved2424&nbsp;=TEXTJOIN(",",TRUE,FILTER(A2:A13,B2:B13&gt;17))Does this return the expected result?&nbsp;

daved2424 · Answer

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

oliverscheurich · Answer

daved2424&nbsp;=TEXTJOIN(",",TRUE,FILTER(Tabelle2[index],Tabelle2[value]&gt;17))If you work with a dynamic table you can reference dynamic columns instead of hard-coded ranges.&nbsp;

daved2424 · Answer

Thank you. I appreciate what you are trying to say, however it is the "&gt;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.

oliverscheurich · Answer

daved2424&nbsp;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.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.&nbsp;

Forum Discussion

Creating comma deliniated list from filtered rows in a table

Share

Resources