• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

TextJoin distinct values

jonastiger

Member
Hi
Hope you're all going well :)

I'm trying to get distinct values using TextJoin function, no sucess.
Please see file example attached:
My formula in K8
Code:
=IF(J8="";"";TEXTJOIN(",";TRUE;IF(MATCH(Table1[SER];Table1[SER];0)=MATCH(ROW(Table1[SER]);ROW(Table1[SER]));Table1[SER];""))*(Table1[LOC]=J8)*(Table1[CL]=$G$7))
I will be very thankful if someone help me to solve this issue.

PS: I work with EXCEL 2019
Thanks
JT
 

Attachments

jonastiger

Member
Hi
Thank you for the quick response.
Your formula returns '#NAME?'. I think you used a O365 function (_xlfn.UNIQUE). Unfortunally, my version is EXCEL2019, as I have mentioned in first post
 

p45cal

Well-Known Member
Attached has:
Solution 1. formula in cells I39 & J39 which spill down by themselves, then a formula in K39 to copy down as far as necessary.
Solution 2. table at cell P7, being a Power Query solution which uses a named range CLTarget (cell G7). Right-click and choose Refresh to update the table if either your source Table1 or cell G7 changes.

edit post posting: just seen your version of Excel doesn't support UNIQUE so my formulae won't work, but the Power Query solution should.
 

Attachments

jonastiger

Member
@Excel Wizard
Your soluction runs perfect :). Thank you very much. I did some atempts with frequency function but not with MATCH together.

@p45cal
Thank you for your approaches. However i think you've done solution 1 with O365 functions (_xlfn.UNIQUE _xlfn._xlws.FILTER) I'm still working with EXCEL2019 :(. Solution 2 is very good, didn't cross my mind to make through power query. Original file has more than 10 queries. I will try that and adapt to original file and compare performance to @Excel Wizard solution

Thank you both very much for your time and help
Bless you
 
Top