• 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


  • 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


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.


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.
= LET(
    values, Normalisedλ(data,nᵣ,sᵣ,dᵣ)(SEQUENCE(N)),
    SORT(UNIQUE(FILTER(values, values<>"")))