Jan 30 2023 05:00 AM
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
Jan 30 2023 05:12 AM
Jan 30 2023 05:56 AM
Jan 30 2023 07:09 AM
=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.
Jan 30 2023 07:25 AM
Jan 30 2023 08:39 AM
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.
Jan 31 2023 01:10 AM
Hmmm ok. My next problem is my values in the values column are text strings so using subtotal would only return zereos.
Jan 31 2023 03:47 AM
=SUBTOTAL(3,[@value])
For text values you can enter this formula in cell C2 in the example.