PDA

View Full Version : [SOLVED] Increment number based on two criteria



ashleyuk1984
08-01-2019, 09:39 AM
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?


24726


=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.

24727

Thank you so much!

Paul_Hossler
08-03-2019, 07:59 AM
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
08-04-2019, 04:39 AM
Thanks Paul,

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