1

I'm trying to create UNIQUE alternative for Excel 2019 as It only works in Office 365, but I don't know where to start.

=TEXTJOIN(", ";1;UNIQUE(IF($B$1:$B$100=A2;$C$1:$C$100;"")))

https://dollarexcel.com/how-to-get-unique-values-without-unique-function/

I've been trying to use this one, but I don't know where should I put the MATCH function in the IF or in place of UNIQUE.

excelhelp
  • 11
  • 2
  • you have TEXTJOIN but not UNIQUE? I ask because there is only one version that is that way and that is Excel 2019. I want to make sure the only issue is the UNIQUE. – Scott Craner Aug 27 '21 at 18:32
  • Yes, the only issue is UNIQUE. It works like I want it to in Office 365, but I have a problem with it in Excel 2019. – excelhelp Aug 27 '21 at 18:33

2 Answers2

2

If TEXTJOIN() is to be used you could create a valid xml-string and use appropriate xpath-syntax to return an array of unique elements through FILTERXML():

enter image description here

Formula in E1:

=TEXTJOIN(", ",,FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,IF(B1:B6=A2,C1:C6,""))&"</s></t>","//s[not(preceding::*=.)]"))

Though non-volatile, there could be some tweaks you may have to make due to the fact xml-input is for example case-sensitive ('A' != 'a') and some characters are harder to process; for more information on that I'd like to redirect you to this post where you can read about that in the bottom-alinea.

JvdV
  • 70,606
  • 8
  • 39
  • 70
1

You will need to use OFFSET which makes it Volatile:

=TEXTJOIN(", ",TRUE,IF((COUNTIFS(OFFSET(C1,,,ROW($ZZ$1:INDEX($ZZ:$ZZ,COUNTA(C:C)))),C1:C22,OFFSET(B1,,,ROW($ZZ$1:INDEX($ZZ:$ZZ,COUNTA(C:C)))),A2)=1)*(B1:B22=A2),C1:C22,""))

as per your local settings you will need to use ; in place of all my ,

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81