# Morefunc - Uniquevalues Alternative

#### ststern45

##### Well-known Member
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}

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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFG
15454492
222222271
392923166
45443854
54715438
671386631
754667122
85431924
938
1066
1192
1231
134
14
Unique
Cell Formulas
RangeFormula
C1:C8C1=UNIQUE(PL)
E1:E8E1=SORT(UNIQUE(PL))
G1:G8G1=SORT(UNIQUE(PL),,-1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
PL=Unique!\$A\$1:\$A\$13G1, E1, C1

#### Peter_SSs

##### MrExcel MVP, Moderator
if you have Excel 365 with the latest dynamic array functions, UNIQUE and SORT is included.
.. otherwise

20 07 26.xlsm
ABCDEFG
154
22254492
392222271
454923166
5443854
671715438
754386631
854667122
93831924
1066
1192
1231
134
14
Unique
Cell Formulas
RangeFormula
C2:C14C2=IFERROR(INDEX(PL,AGGREGATE(15,6,(ROW(PL)-ROW(INDEX(PL,1))+1)/(COUNTIF(C\$1:C1,PL)=0),1)),"")
E2:E14E2=IFERROR(AGGREGATE(15,6,PL/(COUNTIF(E\$1:E1,PL)=0),1),"")
G2:G14G2=IFERROR(AGGREGATE(14,6,PL/(COUNTIF(G\$1:G1,PL)=0),1),"")
Named Ranges
NameRefers ToCells
PL=Unique!\$A\$1:\$A\$13G2:G14, E2:E14, C2:C14

#### ststern45

##### Well-known Member
Thank you for the formulas. Works great.
Also updated my profile.
Thank you

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome.

.. and thanks for the profile update.

Replies
5
Views
120
Replies
6
Views
328
Replies
11
Views
263
Replies
3
Views
214
Replies
4
Views
829

1,181,590
Messages
5,930,778
Members
436,760
Latest member
robgreen43

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back