TEXTJOIN formula with 2 expetions

[Simon.C]

Hi everybody,
I please need your help

In the range W3:AB11 I'm using the VLOOKUP formula to get a text from another page, if it doesn't find anything it will write 0

In this W3:AB11 range I can have some values repeated once, some twice and some multiple times; I also have some 0.

In the cell W18 I want to get, from alle the values in the range W3:AB11 only the text ones, but I need each text value to be written only once


I attach a picture to better show what I need to do. I use excel 2019. In this specific case, I only need to get FISH, SOY, SESAM, GLUTEN



I've been struggling on this formula for a couple of days and I'd really appreciate some help
Thanks in advance

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Simon.C]

Hi, and thanks for the welcome

My bad, I have now attached the file with the formula I'm currently using and few notes about it

Thanks
S

[Glenn Kennedy]

A slightly horrible formula, that will work well in smaller files:

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($W$3:$AB$11)*100+COLUMN($W$3:$AB$11))/(($W$3:$AB$11<>0)*(COUNTIF($W$17:W17,$W$3:$AB$11)=0)),1),"R0C00"),FALSE),"")


or...

a very horrible formula that will work fine in large files. The difference is the use of INDIRECT which recalculates every time anything changes and therefore can cause performance issues.

=IFERROR(INDEX($1:$1048576,AGGREGATE(15,6,ROW($W$3:$AB$11)/(($W$3:$AB$11<>0)*(COUNTIF(X$17:X17,$W$3:$AB$11)=0)),1),10^6*MOD(AGGREGATE(15,6,ROW($W$3:$AB$11)+(COLUMN($W$3:$AB$11)*10^-6)/(($W$3:$AB$11<>0)*(COUNTIF(X$17:X17,$W$3:$AB$11)=0)),1),1)),"")

[Glenn Kennedy]

My file uses the cell references that you gave originally. You can adjust them to suit your sample file if the cell references in YOUR sample file are the ones you're using.

[AliGW]

This would have been so easy using 365!!!

e.g.

=LET(t,TOCOL(C2:H16),TEXTJOIN(", ",,UNIQUE(TRIM(FILTER(t,t<>0)))))

TRIM because there's a trailing space issue with SESAM.

[Jacc]

With Excel 2019 there is no UNIQE function but it can still be done with a formula.
It can be found here: https://www.get-digital-help.com/uni...ay-formulas/#2

There is a sample workbook you can download.

[Simon.C]

Hi Glenn, that's great, Thanks. I was wondering, would it be possible to get the values you have in your file in W14:W17 (or X14:X17) in a single cell, and separate each allergen with a comma and a space?

Thanks again for all the help and the support, to all of you!

[kvsrinivasamurthy]

Pl see file. Data range B3:E11
In H3 copy down.

Please Login or Register  to view this content.

[Glenn Kennedy]

You will need a User-Defined function for that (saved as an xlsm file). Is that acceptable?

[Glenn Kennedy]

I have assumed that it is OK. VBA code:

Please Login or Register  to view this content.

Formula (this will be an array formula in your Excel version):

=ConcatAll(IF(($W$3:$AB$11<>0),$W$3:$AB$11,""),", ",TRUE)


These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Simon.C]

It worked, I really thank everyone of you for your patience and your help!!