Numbered column in vba

Hi,

Seeking a simplified solution to achieve this result.
As follows:

From a list:

40850
40850
40850
40851
40851
40852
40852
40852
40852

what you need

3
2
1
2
1
4
3
2
1

Thank you in advance

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi

You could try this formula.

=COUNTIF($A$1:$A$9,A1)-COUNTIF($A$1:A1,A1)+1

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi,

I messed around a bit 'to run in a loop vba.
I wonder if you can better manage this cycle with an array

Code:

---

Option Explicit
Public Const RigaUp = 2

Sub a()
    Dim RigaDn As Long
    Dim Giorni As Variant
    Dim ciclo As Long
    Dim ciclo1 As Long
    Dim start As Integer
    Dim Old As Integer
    RigaDn = Cells(Rows.Count, 1).End(xlUp).Row
    Giorni = Range(Cells(RigaUp, 4), Cells(RigaDn, 5)).Value
    For ciclo = 1 To UBound(Giorni)
        Giorni(ciclo, 1) = Int(Giorni(ciclo, 1))
    Next
    Range("AA2:AA21") = Giorni
    For ciclo1 = 1 To UBound(Giorni)
        Cells(ciclo1 + 1, 28) = Contig(Range("AA" & ciclo1 + 1 & ":AA22"), Int(Giorni(ciclo1, 1)))
    Next
End Sub

---

Code:

---

Function Contig(ByRef Myaddr As Range, ByVal myVal As String) As Integer
For Each cell In Myaddr
    If cell.Value = myVal Then
        ccnt = ccnt + 1
    Else
        If ccnt > Contig Then Contig = ccnt
        ccnt = 0
    End If
Next cell
End Function

---

Quote:

---

Originally Posted by Admin

Hi

You could try this formula.

=COUNTIF($A$1:$A$9,A1)-COUNTIF($A$1:A1,A1)+1

---

Another formula that can be used is this one...

=COUNTIF(A1:A$9,A1)

Change both A1 references to the cell reference for the cell that your data actually starts at.

Quote:

---

Originally Posted by PcMax

I messed around a bit 'to run in a loop vba.

---

If you really need to do this in VBA (instead of using one of the formulas Admin or I posted earlier), then you can use this macro...

Code:

---

Sub CountDupesBackwards()
  Dim LastRow As Long
  Const DataColumn As String = "A"
  Const OutputColumn As String = "B"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  With Cells(StartRow, OutputColumn).Resize(LastRow - StartRow + 1)
    .Formula = "=COUNTIF(" & DataColumn & StartRow & ":" & DataColumn & _
              "$" & LastRow & "," & DataColumn & StartRow & ")"
    .Value = .Value
  End With
End Sub

---

You could use:

Code:

---

Sub snb()
  sn = Columns(1).SpecialCells(2)
   
  For j = 1 To UBound(sn)
    sn(j, 1) = UBound(Filter(Application.Transpose(sn), sn(j, 1))) + 1
  Next
   
  Columns(1).SpecialCells(2).Offset(, 3) = sn
End Sub

---

Hi,

I have tried and tested various suggestions recommended.
Code Working successfully Thank Snb

Quote:

---

Originally Posted by PcMax

I have tried and tested various suggestions recommended.
Code Working successfully Thank Snb

---

One caution with snb's code... it will work fine as long as all the values in your list are the same length (as you showed in your posting). However, if the length of the list items can vary, and more importantly, if a smaller value can be found as part of a larger value (for example, if your list could have both 1850 and 41850 in it), then snb's code will return incorrect answers.

Unless you adapt the code to that situation (which isn't hard to do)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi,

My research uses the data in a column that I find in columns and refer to a date for equity securities.
If all the data page are ordered and am in uniform, I should not have the problem reported.
If the dates were not in order, however, would get positioning errors of the formulas.
I could use an initial course to test the correctness of the data before running the code.