list and count cities names in a column.

[Manolocs]

Hello, Column A is a large list of cities more than 10k duplicated city names, I need to list the names without duplicate. Preferable with formula only.

 

[Fluff]

How about

Excel 2013/2016
	A	B
1	County
2	Hertfordshire	Hertfordshire
3	Hertfordshire	West Midlands
4	Hertfordshire	Worcestershire
5	Hertfordshire	Warwickshire
6	West Midlands	Staffordshire
7	West Midlands
8	West Midlands
9	Worcestershire
10	Warwickshire
11	West Midlands
12	Warwickshire
13	Worcestershire
14	Staffordshire
Jan2

Cell Formulas
Range	Formula
B2	{=INDEX(A$2:A$32,MATCH(0,COUNTIF(B$1:B1,A$2:A$32),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Manolocs]

that was great any chance to count each city?

 

[Fluff]

In C2
=COUNTIF(A$2:A$32,B2)

 

[Manolocs]

Hi Fluff, sorry I can not figure out how to make the formula you gave me in the top to show more than one city. I tried to copy down but shows the first city again.

 

[Fluff]

Are you sure that the names are the same?
Check for any leading/trailing spaces.

 

[Manolocs]

I mean the array only gives me the first city name, does not give me the list of all of them.
is there any limitation for the array? I set the range to 17000 so:
[FONT=&quot]{=INDEX(A$2:A$17886,MATCH(0,COUNTIF(B$1:B1,A$2:A$17886),0))}
is that correct?

[/FONT]

 

[Fluff]

That should work.
Have you copied the formula down?

 

[Marcelo Branco]

I mean the array only gives me the first city name, does not give me the list of all of them.
is there any limitation for the array? I set the range to 17000 so:
{=INDEX(A$2:A$17886,MATCH(0,COUNTIF(B$1:B1,A$2:A$17886),0))}
is that correct?

Try these steps
1. insert the formula in B2 (must be in this cell);
2. confirm with Ctrl+Shift+Enter simultaneously;
3. and copy down.

M.

 

[Manolocs]

It is working thanks a lot, copy down, save me a lot of time thanks.