Textjoin Calc error help

[Dani_LobP]

Hi,

I seem to be having error when using Textjoin.
The worst part is that it was working fine, but file didn't saved and when I tried to replicate the formula, I keep getting now error Calc and cant see where is the issue.

I have few tables, consisting of 2 columns each. 1 column has list of names, and 2nd column has same value for all. Each table might have same names appearing, or different ones.

Then I have a list with all names appearing in all tables (unique list), and a 2nd column being the name of the 2nd column of the table value (in order to make a summary in which tables the name appears).

So i use

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Table2[x],Table2[Name]=Table1[Name])),UNIQUE(FILTER(Table3[x],Table3[Name]=Table1[Name])))

it should be up to Table7, but basically want to show that a name NOT in Table2 but YES in Table3, is when gives CALC error. Whereas if name is in Table2, will place the result.

table2 name	Table 2 x		Table3 name	Table3 x
Name1	example		Name1	example2
Name3	example		Name2	example2
Name4	example		Name3	example2

Technically i should end up having

Name1	example, example2
Name2	example2 -> but instead, i get #CALC!
Name3	example, example2

When i initially created the formula, it would check table2, if not, would keep checking the rest and then simply put textjoin of all tables where name appears.

I am not sure if i am placing some brackets wrong or using something wrong that I didn't do first time I tried. As I mentioned, it was working great. Not sure what changed from my previous.

Hope it makes sense with example and the way i tried to explain it.

Thanks in advance!

 

[Fluff]

Are you putting that formula in Table1?

 

[Dani_LobP]

Are you putting that formula in Table1?

Hi Fluff,

Yes, in table1, and looks like this:

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Table2[x],Table2[Name]=[@Name])),UNIQUE(FILTER(Table3[x],Table3[Name]=[@Name])))

 

[Fluff]

That's a different formula from what you originally posted, does it work?

 

[Dani_LobP]

That's a different formula from what you originally posted, does it work?

No, gives CALC error, different how? just the [name] , that in the example wanted to express it was name from table 1, or what else is different? cant really see...

 

[Fluff]

In your op you were trying to match the name to the entire name column of table 1, not just a single cell.

If you are getting #calc errors then there are not matches in at least one of the other tables.
Try it like

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Table2[x],Table2[Name]=[@Name],"")),UNIQUE(FILTER(Table3[x],Table3[Name]=[@Name],"")))

 

[Dani_LobP]

In your op you were trying to match the name to the entire name column of table 1, not just a single cell.

If you are getting #calc errors then there are not matches in at least one of the other tables.
Try it like

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Table2[x],Table2[Name]=[@Name],"")),UNIQUE(FILTER(Table3[x],Table3[Name]=[@Name],"")))

Oh right! that was it... the leave empty part... **** I couldn't see it.
Thanks once again Fluff! you the best!

 

[Fluff]

Glad to help & thanks for the feedback.