Incremental numbers

[GordonLS]

I need to incrementally increase the number in column A for every unique record in column B. In other words, Cell A1 has the number 1, Cell B1 has the name John. John is in the 1st 10 rows of column B and all Johns receive the number 1 in column A. Cell B11 has the name Cindy, and this needs the number 2 in column A and so forth.

Thank you

 

[Fluff]

How about

=SUMPRODUCT(1/(COUNTIFS(B$2:B2,B$2:B2)))

 

[GordonLS]

Worked nicely.

Thank you much.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[GordonLS]

Is there another method. Not sure why but this is extremely slow. I have 20,000 records and it just crawls. Cant get it to complete.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1	count	County
2	1	County Durham
3	1	County Durham
4	1	County Durham
5	2	Derbyshire
6	2	Derbyshire
7	3	Devon
8	4	Dorset
9	5	Gloucestershire
10	5	Gloucestershire
11	6	Greater Manchester
12	7	Hertfordshire
13	8	Lancashire
14	8	Lancashire
15	8	Lancashire
16	9	North Yorkshire
17	9	North Yorkshire
18	9	North Yorkshire
19	10	Somerset
20	11	South Yorkshire
21	11	South Yorkshire
22	11	South Yorkshire
23	11	South Yorkshire
24	12	Staffordshire
25	12	Staffordshire
26	12	Staffordshire
27	13	Surrey
28	13	Surrey
29	14	West Midlands
30	14	West Midlands
Lists

Cell Formulas
Range	Formula
A2:A30	A2	=IF(B2=B1,A1,MAX(A$1:A1)+1)

 

[GordonLS]

I will give that a shot. Not sure if it's just my computer. The file has 50,000+ records

 

[Fluff]

With that amount of rows, a lot of formulae will be slow.