Morefunc - Uniquevalues Alternative

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
958
Office Version
  1. 365
  2. 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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
Thank you for the formulas. Works great.
Also updated my profile.
Thank you
 
Upvote 0
You're welcome. :)

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

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top