Forum Discussion
Solved
Concatenate IF help
Hi everyone, I have been trying to use concatenate with the IF function to generate product allergens by ignoring the word none. I basically have: Column K has the below Allergen Informat...
Aaargh, it's not my day. Wrong order of the functions.
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(K23:K35,K23:K35<>"None","None")))
Hi there, thanks for the quick reply!
I have Excel in Microsoft 365, tried the formula you suggested and I get a #VALUE! error 😞
I have Excel in Microsoft 365, tried the formula you suggested and I get a #VALUE! error 😞
My apologies, it should have been
=TEXTJOIN(", ", TRUE, IF(K23:K35="None", "", K23:K35))
=TEXTJOIN(", ", TRUE, FILTER(K23:K35, K23:K35<>"None"))
- Ok so
1) =TEXTJOIN(", ", TRUE, IF(K23:K35="None", "", K23:K35))
duplicate allergens still appear and when column K is changed to have none in all the cells I get a #CALC! error
2) =TEXTJOIN(", ", TRUE, FILTER(K23:K35, K23:K35<>"None"))
duplicate allergens still appear and when column K is changed to have none in all the cells the cell is just empty
Is there a way to incorporate another function in this TEXTJOIN you gave me where it will give
all the alergens separated by a comma and space as well as:
- the word none if all my allergen collumn has the word none on every cell,
- ignore any duplicate or triplicate allergens
Or is there any other function that can do the above? 😞- Solved the appearance of "none" when all the cells are "none" but I still get duplicate allergens, so when I have my column as
Allergen Information
Cochineal
Milk
None
None
None
Milk
Milk
None
None
None
None
None
None
with your formula I get:
Cochineal, Milk, Milk, Milk
When I want to get just:
Cochineal, Milk
Is this achievable at all?