• 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.

Unique distinct list from five columns

UniqueUsername1

New Member
Hello! This is my first post here so please tell me if I'm doing something wrong.
I use a variation of the array formula from the link below to get a unique distinct list:
This has worked wonderfully until now, but now I have to do the same thing with five columns. Unfortunately, every solution I could find online required the columns to be adjacent. This is not possible in my sheet. For security reasons, my organization does not allow VBA or Macros.
I have attached the sample sheet. I had to delete all unrelated data as it's sensitive. Please note that there is data in every column up to A:A.
Any help would be appreciated. Thanks in advance.
 

Attachments

Peter Bartholomew

Well-Known Member
Pity you are not using 365. I wrote a Lambda function that reads a multidimensional array given the number of items nᵣ within each dimension r, the stride length separating adjacent terms of the array sᵣ, and the direction dᵣ. Given that, it forms a single column of values. The array can then be sorted and filtered as required.
Code:
= LET(
    values, Normalisedλ(data,nᵣ,sᵣ,dᵣ)(SEQUENCE(N)),
    SORT(UNIQUE(FILTER(values, values<>"")))
  )
77484
 
Top