VBA to Assign category numbers

[BORUCH]

hi all
i have an excel sheet that looks like below

I'm looking for a VBA that would automate for me column D and assign category numbers starting with 001 002 etc..

the criteria is if they are are all the same price, but also right underneath each other so for example the item number 00001 and item number c123 are both 100 dollars but not the same group

there will always be an empty row between each group

any help is greatly appreciated

thanks

 

[BORUCH]

Try this

Sub AllocateCategories_v3()
  Dim Bits As Variant
  Dim rA As Range
  Dim oSet As Long, HdrRow As Long
  Dim PCol As String, RCol As String, Resp As String
 
  Resp = InputBox("Enter header row, Price column & Result column separated by commas. eg 1,J,O")
  Bits = Split(Resp, ",")
  If UBound(Bits) = 2 Then
    If Evaluate("isref(" & Bits(1) & Bits(0) & ")") And Evaluate("isref(" & Bits(2) & Bits(0) & ")") Then
      HdrRow = Bits(0)
      PCol = Bits(1)
      RCol = Bits(2)
      oSet = Columns(PCol).Column - Columns(RCol).Column
      Application.ScreenUpdating = False
      With Range(RCol & HdrRow + 1).Resize(Range(PCol & Rows.Count).End(xlUp).Row - HdrRow)
        .NumberFormat = "000"
        For Each rA In .Offset(, oSet).SpecialCells(xlConstants).Areas
          rA.Offset(, -oSet).FormulaR1C1 = _
            Replace("=IF(COUNTIF(" & rA.Address(, , xlR1C1) & ",RC[#])=1,"""",IFNA(VLOOKUP(RC[#],R" & rA.Row - 1 & "C[#]:R[-1]C,-#+1,0),MAX(R1C:R[-1]C)+1))", "#", oSet)
        Next rA
        .Value = .Value
      End With
      Application.ScreenUpdating = True
    Else
      MsgBox "Incorrect input"
    End If
  Else
    MsgBox "Incorrect input"
  End If
End Sub

Sorry for late response this is perfect
thanks again

 

[Peter_SSs]

You're welcome. Would that mean you change the 'Mark as solution' post?

 

[BORUCH]

You're welcome. Would that mean you change the 'Mark as solution' post?

Yes i just changed it

 

[BORUCH]

Sorry for late response this is perfect
thanks again

hi
i was thinking what happens if the item number is duplicated although rare in my case but it could happen, the solution would be in my case to just put the categories on whichever price is the lowest

 

[Peter_SSs]

Sounds like quite a different question so I would start a new thread, link to this one if you want, and most importantly provide some varied sample data and expected results in a form that helpers can copy/paste so they are not faced with typing it all out manually.