Unique name list

[Salamullah]

Hi

I know pivot table can bring the result but I need formula to bring unique name list in column B from list of Names in column A, please share any formula

Column A

John
Ben
John
Ben
Ben
John
Peter
Peter

Column B
John
Ben
Peter

 

[Fluff]

How about

Excel 2013/2016
	A	B
1	District
2	St Albans	St Albans
3	St Albans	Welwyn Hatfield
4	St Albans	Dacorum
5	St Albans	Somercotes
6	St Albans
7	Welwyn Hatfield
8	Welwyn Hatfield
9	Welwyn Hatfield
10	Welwyn Hatfield
11	St Albans
12	St Albans
13	St Albans
14	St Albans
15	Dacorum
16	St Albans
17	St Albans
18	St Albans
19	St Albans
20	Somercotes
TB1

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(A$2:A$20,MATCH(0,INDEX(COUNTIF(B$1:B1,A$2:A$20),0),0)),"")

 

[Salamullah]

its giving zero in column B

 

[Fluff]

Does your data start in A2 & did you put the formula in B2?

 

[Salamullah]

no
=IFERROR(INDEX(Q$11000:Q$14000,MATCH(0,INDEX(COUNTIF(BG$3:BG4,Q$11000:Q$14000),0),0)),"")

 

[Fluff]

What is the first cell that contains the formula?

 

[Salamullah]

BG3 is the 1st cell for formula and data range is q11000 to q14000

 

[Fluff]

In that case it needs to be
=IFERROR(INDEX(Q$11000:Q$14000,MATCH(0,INDEX(COUNTIF(BG$2:BG2,Q$11000:Q$14000),0),0)),"")

 

[Salamullah]

its working many thanks

 

[Fluff]

You're welcome & thanks for the feedback