Join multiple rows values in same cell, based on 2 conditions.

[Dani_LobP]

Hi,

I am trying to find the way to obtain a list of values in same cell, based on 2 conditions.
I seem to be struggling with the right way based on below formula:

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(ColumnB,(ColumnA=A2)*(ColumnC="Yes"),"")))

for some reason i obtain only 1 result instead of as in example below:

A	B	C	D
Item1	Country1	Yes	Country1, Country5
Item2	Country2	No
Item3	Country3	Yes
Item4	Country4	No
Item1	Country5	Yes	Country1, Country5
Item2	Country6	No
Item3	Country7	Yes
Item4	Country8	No
Item1	Country9	No
Item2	Country10	No
Item3	Country11	Yes
Item4	Country12	No

thanks in advance!

 

[etaf]

item 3 in row 3 , that has a YES , why has than not returned some results

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER($B$2:$B$100,(($A$2:$A$100=A2)*($C$2:$C$100="Yes")),"")))

Book3
	A	B	C	D	E	F	G	H
1	A	B	C	D
2	Item1	Country1	Yes	Country1, Country5			Country1, Country5
3	Item2	Country2	No
4	Item3	Country3	Yes				Country3, Country7, Country11
5	Item4	Country4	No
6	Item1	Country5	Yes	Country1, Country5			Country1, Country5
7	Item2	Country6	No
8	Item3	Country7	Yes				Country3, Country7, Country11
9	Item4	Country8	No
10	Item1	Country9	No				Country1, Country5
11	Item2	Country10	No
12	Item3	Country11	Yes				Country3, Country7, Country11
13	Item4	Country12	No
14
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER($B$2:$B$100,(($A$2:$A$100=A2)*($C$2:$C$100="Yes")),"")))

 

[Dani_LobP]

item 3 in row 3 , that has a YES , why has than not returned some results

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER($B$2:$B$100,(($A$2:$A$100=A2)*($C$2:$C$100="Yes")),"")))

hi, thanks from prompt reply.
item 3 in row 3 indeed has Yes, i was just making some example manually so it was easy to understand my explanation in case of not being clear.
you are right about the result that should have.

So can it be that my mistake, is instead of using a range B2:B100, i am using the whole column? when i click on the cell, instead of B2, is appears the title of the column.
Does it make any difference for this purpose?
If apply formula using columns from table, i get single results, it wont join anything.

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER([ColumnB],(([ColumnA]=A2)*([ColumnC]="Yes")),"")))

And if i apply your formula (using ranges) i will get results even when there is "No"... does it make sense?

i tried adding starter condition as maybe workaround... does it make sense?

=IF([ColumnC]="Yes",TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B100,((A2:A100=A2)*(C2:C100="Yes")),""))),"")

 

[etaf]

it still works - with full column reference A:A etc
But i prefer to just use a range , as in excel using A:A is a huge range

Book3
	A	B	C	D	E	F	G	H
1	A	B	C	D
2	Item1	Country1	Yes	Country1, Country5			Country1, Country5
3	Item2	Country2	No
4	Item3	Country3	Yes				Country3, Country7, Country11
5	Item4	Country4	No
6	Item1	Country5	Yes	Country1, Country5			Country1, Country5
7	Item2	Country6	No
8	Item3	Country7	Yes				Country3, Country7, Country11
9	Item4	Country8	No
10	Item1	Country9	No				Country1, Country5
11	Item2	Country10	No
12	Item3	Country11	Yes				Country3, Country7, Country11
13	Item4	Country12	No
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B:B,((A:A=A2)*(C:C="Yes")),"")))

If apply formula using columns from table, i get single results, it wont join anything.

not sure on tables - i tend to get mixed results and often just use a range

BUT - i have tried and seems ok

Book3
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D						USING TABLE
2	Item1	Country1	Yes	Country1, Country5			Country1, Country5			Country1, Country5
3	Item2	Country2	No
4	Item3	Country3	Yes				Country3, Country7, Country11			Country3, Country7, Country11
5	Item4	Country4	No
6	Item1	Country5	Yes	Country1, Country5			Country1, Country5			Country1, Country5
7	Item2	Country6	No
8	Item3	Country7	Yes				Country3, Country7, Country11			Country3, Country7, Country11
9	Item4	Country8	No
10	Item1	Country9	No				Country1, Country5			Country1, Country5
11	Item2	Country10	No
12	Item3	Country11	Yes				Country3, Country7, Country11			Country3, Country7, Country11
13	Item4	Country12	No
14
15
Sheet1

Cell Formulas
Range	Formula
J2:J12	J2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Table1[B],((Table1[A]=A2)*(Table1[C]="Yes")),"")))
G2:G13	G2	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B:B,((A:A=A2)*(C:C="Yes")),"")))

And if i apply your formula (using ranges) i will get results even when there is "No"... does it make sense?

NO, but look at the brackets , just to ensure they are the same as mine

i tried adding starter condition as maybe workaround... does it make sense?

No need to do that - it should work as i have written

 

[Dani_LobP]

Ok I just realized what was going on.
Its giving me results in lines where Item is even No or Empty, but just because that same item has at some place a country and Yes, so it provides me that country with yes, for all equal items.
All good. Took me a bit to realize my own logic :D

Thanks a lot for the support etaf!

 

[etaf]

you are welcome