Morefunc - Uniquevalues Alternative

[ststern45]

Hello everyone,

I wanted to ask if there may be an alternative to the Morefunc add-in formula "Uniquevalues"
Using the example below:
The range "PL" contains the items 54, 22, 92, 54, 4, 71, 54, 54, 38, 66, 92, 31 and 4
Where these values above are located in cell range A1:A13.
returns {92;71;66;54;38;31;22;4}
or
returns {4;22;31;38;54;66;71;92}

Thank you in advance!!

SYNTAX :

{=UNIQUEVALUES(Array,Order)}

Can be a range reference (A1:F1500), a calculated array (A1:F500+H1:M500) or a constant array ({12,5;4,2;23,4}). It can contain any data type (numbers, text, boolean or error codes, empty cells). Maximal size : 65 535 items or cells.

The resulting array has only one column, regardless of the shape of the argument.
EXAMPLES :

The range "PL" contains the items 54, 22, 92, 54, 4, 71, 54, 54, 38, 66, 92, 31 and 4.

{=UNIQUEVALUES(PL)} returns {92;71;66;54;38;31;22;4}

{=UNIQUEVALUES(PL,1)} returns {4;22;31;38;54;66;71;92}

The 5 duplicate items of the range are replaced with empty strings put at the end of the returned array.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 with the latest dynamic array functions, UNIQUE and SORT is included.

20 07 26.xlsm
	A	B	C	D	E	F	G
1	54		54		4		92
2	22		22		22		71
3	92		92		31		66
4	54		4		38		54
5	4		71		54		38
6	71		38		66		31
7	54		66		71		22
8	54		31		92		4
9	38
10	66
11	92
12	31
13	4
14
Unique

Cell Formulas
Range	Formula
C1:C8	C1	=UNIQUE(PL)
E1:E8	E1	=SORT(UNIQUE(PL))
G1:G8	G1	=SORT(UNIQUE(PL),,-1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
PL	=Unique!$A$1:$A$13	G1, E1, C1

 

[Peter_SSs]

if you have Excel 365 with the latest dynamic array functions, UNIQUE and SORT is included.

.. otherwise

20 07 26.xlsm
	A	B	C	D	E	F	G
1	54
2	22		54		4		92
3	92		22		22		71
4	54		92		31		66
5	4		4		38		54
6	71		71		54		38
7	54		38		66		31
8	54		66		71		22
9	38		31		92		4
10	66
11	92
12	31
13	4
14
Unique

Cell Formulas
Range	Formula
C2:C14	C2	=IFERROR(INDEX(PL,AGGREGATE(15,6,(ROW(PL)-ROW(INDEX(PL,1))+1)/(COUNTIF(C$1:C1,PL)=0),1)),"")
E2:E14	E2	=IFERROR(AGGREGATE(15,6,PL/(COUNTIF(E$1:E1,PL)=0),1),"")
G2:G14	G2	=IFERROR(AGGREGATE(14,6,PL/(COUNTIF(G$1:G1,PL)=0),1),"")

Named Ranges
Name	Refers To	Cells
PL	=Unique!$A$1:$A$13	G2:G14, E2:E14, C2:C14

 

[ststern45]

Thank you for the formulas. Works great.
Also updated my profile.
Thank you

 

[Peter_SSs]

You're welcome.

.. and thanks for the profile update.