Alternative for UNIQUE in Excel 2013

[Dhinakaran]

Hello,

As mentioned in the subject, is there alternative to extract unique values in Excel 2013 since the UNIQUE function isn't supported in older versions ?

 

[arthurbr]

One possibility is using Power Query ( free add in at Download Microsoft Power Query for Excel from Official Microsoft Download Center - no MS support anymore

 

[shift-del]

Hi

There is the CSE add in from Flotter Feger (Sabina). Never tried it myself.

MS-Office-Forum - Einzelnen Beitrag anzeigen - ArrayFormeln für 2010, 2013, 2016, 2019

Forum für Microsoft Office, Access, Excel, Word, Visual Basic, .NET, SQL u.v.a. - Tipps kostenlos und free Download

www.ms-office-forum.net

(Post is in German. You need to have an account at ms-office-forum.net)

 

[Fluff]

Another option

Fluff.xlsm
	A	B
1	County
2	West Yorkshire	West Yorkshire
3	Surrey	County Durham
4	West Yorkshire	Somerset
5	County Durham	Dorset
6	South Yorkshire	South Yorkshire
7	Somerset	Lancashire
8	Gloucestershire	Cornwall
9	Dorset
10	County Durham
11	West Yorkshire
12	South Yorkshire
13	South Yorkshire
14	Lancashire
15	Greater Manchester
16	Northumberland
17	Lancashire
18	Hampshire
19	Derbyshire
20	Cornwall
21	West Yorkshire
Data

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$21)/ISNA(MATCH($A$2:$A$21,B$1:B1,0)),ROWS(B$2:B2))),"")

 

[Dhinakaran]

Thanks everyone for your answers. Please consider the below table for instance and suggest the formula to get the unique values (using Office 2013).

Procurement
Website
Website
Sales
Finance
Website
Website
Website
Website
Finance
Finance
Website
Finance
Sales
Finance
Finance

 

[Fluff]

Have you tried the formula in post#4?

 

[Domenic]

I think @Fluff's formula needs a slight adjustment. I think ROWS(B$2:B2) should be replaced with 1, and then all of the unique values should be returned.

Hope this helps!

 

[Fluff]

@Domenic good spot, it should indeed be 1

Fluff.xlsm
	A	B
1
2	Procurement	Procurement
3	Website	Website
4	Website	Sales
5	Sales	Finance
6	Finance
7	Website
8	Website
9	Website
10	Website
11	Finance
12	Finance
13	Website
14	Finance
15	Sales
16	Finance
17	Finance
18
Data

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$17)/ISNA(MATCH($A$2:$A$17,B$1:B1,0)),1)),"")

 

[alansidman]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Procurement"}, {{"Count", each _, type table [Procurement=text]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"})
in
    #"Removed Columns"