Remove duplicate with textjoin and including criteria

Asked Dec 18 '20 at 23:44

Active Dec 19 '20 at 21:36

Viewed 130 times

I have a situation that has been killing me. So here's the dataset

I want to do the text join to give me all countries with sale more than 0. I got the formula below

{=TEXTJOIN(", ",,IF(B2:B6>0,IF(MATCH(A2:A6,A2:A6,0)=ROW(A2:A6)- ROW(A2)+1,A2:A6,""),""))}

So the expected result is ca, se, uk, us

What is really weird is right now, the result is se, uk, us. But if I change the sales to the below, it works

So my guess is if the first row doesn't meet the criteria (b2:b6>0), then it will not return that country once and for all, even though the entries after that do meet the criteria. Do you have any idea how to fix this?

edited Dec 19 '20 at 21:36

JvdV

70,606
8
39
70

asked Dec 18 '20 at 23:44

Long N

In office 365 you can combine TEXTJOIN with UNIQUE and FILTER. – P.b Dec 19 '20 at 19:07
`=TEXTJOIN(", ",1,UNIQUE(FILTER(A2:A6,B2:B6>0)))` would give your expected result in office 365 – P.b Dec 20 '20 at 09:31
Hi P.b I don't use Office365 but Excel 2019. Unique and Filter don't work ehre – Long N Dec 21 '20 at 20:56
What does this do: ```IF(MATCH(A2:A6,A2:A6,0)=ROW(A2:A6)- ROW(A2)+1,A2:A6,"")``` ? Result of this formula is always true. – Pavol Velky Dec 25 '20 at 20:01

Remove duplicate with textjoin and including criteria

0 Answers0