0

Lets say that we have a table in excel. And someone applies a filter using ui (from the arrow in one column) to the whole table.

So from this filtered table i want to textjoin a column (the filtered rows).

Lets say we have table1 and i want to text join column Language.

if i use function

=TEXTJOIN(",";1;Table1[[#Data];[Language]])

the result joins all the rows of the column irrelevant if it is filtered, and subtotal i think can not work here.

What can i do so i can join all rows from a filtered table so everytime the filter changes the result of the textjoin will change?

So lets say that this is the column that i want to textjoin and someone filters out the first and the third row. So i want the textjoin to ignore these rows

enter image description here

kyrpav
  • 756
  • 1
  • 13
  • 43
  • Can you show an example of the data so we can understand the problem? If you're joining a text-formatted cell, it's unclear how that would have a negative effect on the table or the sums for your setup. – PeterT Jan 10 '20 at 14:56

2 Answers2

0

i think this will do it:

=TEXTJOIN(",";1;IF(SUBTOTAL(103;OFFSET(Table1[[#Data];[Language]];ROW(Table1[[#Data];[Language]])-MIN(ROW(Table1[[#Data];[Language]]));;1))=1;Table1[[#Data];[Language]];""))

where the subtotal with the offseet filters out role by role if something is hidden and returns the proper fields so textjoin works

kyrpav
  • 756
  • 1
  • 13
  • 43
0

I add a column called "Visible" to all my tables.

Example in a table named "tabPays"

Formula for the column "Visible": =AGGREGATE(3;5;[@Pays])

Then it's easy to do many things related to filtered rows. The column "Visible" contains 1 when the row is visible, and 0 if not.

The answer of your question is: =TEXTJOIN(", ";TRUE;FILTER(tabPays[Pays];tabPays[Visible]=1))

user673679
  • 1,327
  • 1
  • 16
  • 35
Sylvie Biard
  • 1
  • 1