Forum Discussion
Creating comma deliniated list from filtered rows in a table
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...
Unfortunately not as that requires hard-coding the filter value into the formula. I need it to dynamically update as filters are applied.
=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.
- 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.
=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.