Morefunc - Uniquevalues Alternative

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
814
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 17, 2005
Messages
814
Office Version
  1. 2010
Platform
  1. Windows
Thank you for the formulas. Works great.
Also updated my profile.
Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
You're welcome. :)

.. and thanks for the profile update. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top