CODEWITHSUNDEEP
excelexcel-formula
Kai
Kai

Reputation: 63

Textjoin without duplicates

I would like to seek your help for below formula, I have a table of cells that will return either of these 3 values (Yes/No/N/A). I am trying to summarize the header of the cells that return "No" without including duplicates however its not returning all the unique headers. In the below screenshots only 600000 is returned instead of "Cost Center, Carrier (E), Service Level (DR), Consignee (M), 600000", formula in array or not makes no difference. I have been searching the internet for an answer to no avail, any help or advice is very much appreciated!

enter image description here enter image description here

=TEXTJOIN(", ",TRUE,IF(C3:BH3="No",IF(MATCH($C$1:$BH$1,$C$1:$BH$1,0)=(COLUMN($C$1:$BH$1)-COLUMN($B$1)),$C$1:$BH$1,""),""))

Upvotes: 0

Views: 348

Answers (1)

Spectral Instance
Spectral Instance

Reputation: 2494

Well, if you have FILTER() and UNIQUE() then this would work

=TEXTJOIN(",",TRUE,UNIQUE(TRANSPOSE(FILTER(AJ1:AQ1,AJ3:AQ3="No"))))

but I had understood from your array talk that you were on 2019...

Upvotes: 3

Related Questions