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

#### Attachments

• 11.1 KB Views: 10

##### New Member
I should mention that I use Excel 2019.

#### bosco_yip

##### Excel Ninja
In R3, formula copied down :

=IFERROR(SMALL((D\$3:D\$25,G\$3:G\$25,D\$29:D\$51,G\$29:G\$51),1+SUMPRODUCT(COUNTIF(R\$2:R2,CHOOSE({1,2,3,4},D\$3:D\$25,G\$3:G\$25,D\$29:D\$51,G\$29:G\$51)))),"")

##### New Member
In R3, formula copied down :

=IFERROR(SMALL((D\$3:D\$25,G\$3:G\$25,D\$29:D\$51,G\$29:G\$51),1+SUMPRODUCT(COUNTIF(R\$2:R2,CHOOSE({1,2,3,4},D\$3:D\$25,G\$3:G\$25,D\$29:D\$51,G\$29:G\$51)))),"")

View attachment 77473
Thank you! Not only did you simplify the formula, you made it easier to add new ones as well.

#### 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<>"")))
)``````