Running count of occurrence in list

[vikas_newports]

Hello
I have more than 50000 records in my sheet and I am using the below formula to count the running occurrence of record

=COUNTIF($A$2:A2,A2)

but it hangs my system and takes more than 5 min to run the formula
Is there any way to fast this process?

Prefer option is simple excel, the other option is VBA for solution

 

[vikas_newports]

All records have 1

 

[jasonb75]

Has your friend entered the formula into the correct column? It would return all 1's if it was entered into C2 instead of B2

 

[Peter_SSs]

All records have 1

Even on a small set sample data like this?

vikas_newports.xlsm
	A	B
1	Data	Count
2	6	1
3	4	1
4	6	2
5	5	1
6	6	3
7	6	4
8	abc	1
9	4	2
10	abc	2
Sheet3

Cell Formulas
Range	Formula
B2:B10	B2	=XLOOKUP(A2,A$1:A1,B$1:B1,0,0,-1)+1

 

[vikas_newports]

@Peter_SSs , yes inputed the formula correctly in B2 cell but I discovered that my friend is testing on the data which is starting with Alphabets. e.g

ABC1827600046
ABC1827700028
ABC1827800033
ABC1828100001
ABC1828200005
ABC1828200023

instead of

12
12
13
12
14
12

So it is working if the data is starting with Numerics

 

[Peter_SSs]

data which is starting with Alphabets.

That should make no difference at all.

Are you sure that your friend actually has duplicate vales in the sample data?

vikas_newports.xlsm
	A	B
1	Data	Count
2	ABC1827600046	1
3	ABC1827700028	1
4	ABC1827800033	1
5	ABC1828100001	1
6	ABC1828200005	1
7	ABC1828200023	1
8	ABC1827700028	2
9	ABC1827700028	3
10	ABC1828200023	2
Sheet3

Cell Formulas
Range	Formula
B2:B10	B2	=XLOOKUP(A2,A$1:A1,B$1:B1,0,0,-1)+1

 

[jasonb75]

I found this about 10 times faster than either of the formulas using COUNTIF.

I think that there are a lot of variables to be considered with this. I tried the xlookup suggestion with ~45k records, (initial record set of ~5.5k records replicated 8 times with copy & paste, record frequency varied from unique to 200 repetitions in the original data set).

I didn't do exact timing, but from my test.

The original countifs method from post 1 and my alternative both took about 10 seconds.
Vba method was almost instant.
Xlookup took best part of 10 minutes.

 

[vikas_newports]

@jasonb75, I agree with you xlookup is taking much more time than your formula., No doubt vba solution is instant.

 

[Peter_SSs]

I think that I must have a lot more repetitive data than you two & perhaps then the XLOOKUP was finding a match much quicker.
In any case we have already established that the vba solution is very fast and is an acceptable method so let's just stick with that.