Filter by another field

[IF]

Hi,

I have two tables. Table 1:

NameType

A	X1
A	X1
B	X2
B	X3
B	X4
C	X2
C	X3
C	X3
C	X1
D	X1

Table2:

NameQty

A	2
B	3
C	5
D	7
E	8

I want to connect the tables with the "name" column. If the name has X1 type value, I want to filter that name. In table 1, I have X1 value for A, C, D. Only Name:B doesn't have X1 type. Based on this I want to have a table, that shows Name and Qty, which should be B and the qty should be 3.

how can i a filter in 

[Anonymous · Accepted solution]

Hi @IF ,

I got this:

First you have to group Type by Name all rows, you would get this table:

Then you have to get the column of Type from this column table as list, you can do it with this formula:

Then you have to expand the list with "," delimited for example and remove the column table, you would have your table like this:

Then you just have to merge with the other table by Name column, and filer the column of Types by Text Contain, and i get that i think you need: