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

 

[jasonb75]

With a formula, the best you could do is a logical test to reduce the load when you have consecutive rows of identical data.
Assuming first formula is going into B2 (the reference to B1 needs to refer to the cell immediately above the first formula).

=IF(A2=A1,B1+1,COUNTIF($A$2:A2,A2))

If the data is completely random then this could be counterproductive.

 

[Peter_SSs]

If the results do not need to be dynamic (that is the data in column A is not changing) then try this with a copy of your workbook.

Sub Number_Items()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  Set d = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) + 1
    a(i, 1) = d(a(i, 1))
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub

 

[vikas_newports]

Thanks @Peter_SSs for the fastest solution. I would use it as a personal macro
Thanks @jasonb75 you formula is also fast but I need to paste special it in the end.

 

[Peter_SSs]

Thanks @Peter_SSs for the fastest solution. I would use it as a personal macro
... I need to paste special it in the end.

Given the last part above, clearly the macro solution is the fastest. However, if you did want to test a formula solution and you are prepared to use your Excel 365 not Excel 2010, I found this about 10 times faster than either of the formulas using COUNTIF.

=XLOOKUP(A2,A$1:A1,B$1:B1,0,0,-1)+1

 

[vikas_newports]

@Peter_SSs
it is giving #Name? error I input the formula in B2 cell
Edit: Sorry I have not excel 365 right now but in my workplace I will try it.

 

[vikas_newports]

@Peter_SSs , it is only giving 1 against every record
Formula cell = B2

 

[Peter_SSs]

@Peter_SSs , it is only giving 1 against every record
Formula cell = B2

Are you on Excel 365 now? It will only work there.
Also, it may still take a while to calculate your whole column & display the correct results. Test it on a small sample of say 10 varied values in column A.

 

[vikas_newports]

@Peter_SSs , yes my friend is available in office right now and I requested him to try it and he confirmed the result

 

[Peter_SSs]

and he confirmed the result

Which result?
- Correct results or
- All 1s