Increment number based on two criteria

[ashleyuk1984]

Hi,

I have a list of Descriptions and Package Types... I'm trying to give them distinct numbers based on the criteria....

Similar to this:

AA = 1
BB = 2
CC = 3
AA = 1
AA = 1
BB = 2

I hope that makes sense.

But my criteria is based across two columns (description and package type). I have what looks like a working formula for the most part, but for some reason one of the rows isn't calculating correctly?? And I can't work out why?


pic1.jpg

=IF(B3>0,IF(COUNTIFS(B$3:B3,B3,C$3:C3,C3)=1,MAX(D$2:D2)+1,VLOOKUP(B3,B$2:D2,3,0)),"")

I'm wondering if my formula could be either fixed, or if there are any other formulas that could essentially do the same job, if not better? I'm probably over thinking this!!

I've attached a sample workbook.

Cargo Logic Workbook.xlsx

Thank you so much!

[Paul_Hossler]

Not a worksheet formula, but something to consider

Option Explicit

Sub drv()
    Call CargoNumbers(Range("B:C"), Range("F:F"))
End Sub



Sub CargoNumbers(DescripPkgType As Range, CargoLogic As Range)
    Dim r1 As Range, r2 As Range
    Dim C As Collection
    Dim iIn As Long, iIndex As Long
    Set C = New Collection
    
    Set r1 = DescripPkgType.SpecialCells(xlCellTypeConstants, xlTextValues)
    Set r2 = Intersect(r1.EntireRow, CargoLogic)
    For iIn = 1 To r1.Rows.Count
        If r1.Cells(iIn, 1).Value <> "DESCRIPTION" And Len(r1.Cells(iIn, 1).Value) <> 0 Then
            On Error Resume Next
            C.Add r1.Cells(iIn, 1).Value & "#" & r1.Cells(iIn, 2).Value, r1.Cells(iIn, 1).Value & "#" & r1.Cells(iIn, 2).Value
            On Error GoTo 0
        End If
    Next iIn
    For iIn = 1 To r1.Rows.Count
        If r1.Cells(iIn, 1).Value <> "DESCRIPTION" And Len(r1.Cells(iIn, 1).Value) <> 0 Then
            For iIndex = 1 To C.Count
                If C(iIndex) = r1.Cells(iIn, 1).Value & "#" & r1.Cells(iIn, 2).Value Then
                    r2.Cells(iIn, 1).Value = iIndex
                    Exit For
                End If
            Next iIndex
        End If
    Next iIn
End Sub

[ashleyuk1984]

Thanks Paul,

I ended up concatenating the columns and then running a similar formula for just one column.