Need to flag the lead ID of any contiguous ID's from a sorted list of ID's.

[JeanPierre Charron]

Within a sorted list of non contiguous alphanumeric ID's, I often have some ID's that are contiguous.
I need to flag with an "X" in the adjacent column, the lead ID of any contiguous group,
or isolated ID if not contiguous.
..
I assume that with a loop thru my sorted list
I will store the current record in three variables:
ID, ID Length, Type (Numeric or Alpha)
..
IF Next Record Has same ID length and Same type as the Current Record
IF Next Record ID Value = Current Record Value + 1 Then Flag current Record with "X"
Loop
ENDIF
etc...
..
For instance :
ID Flag Comment
111 x is a lead
112
113
115 X Complex Case involving a similar numeric & alphanumeric
115A
123 x is isolated
250A x is a lead
250B
300A1 X is a lead
300A2

I need the VBA Code to get me started thru looking at the next record while being at the current record to compare if it is the next sequential or not.
Thank you for your help,
J.P.

 

[Claus Busch]

Hi J.P.,

Am Wed, 8 Oct 2014 12:22:50 -0700 (PDT) schrieb JeanPierre Charron:

For instance :
ID Flag Comment
111 x is a lead
112
113
115 X Complex Case involving a similar numeric & alphanumeric
115A
123 x is isolated
250A x is a lead
250B
300A1 X is a lead
300A2

If there are no other "special" cases try:

Sub SetFlag()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Range("B2") = "x"

For Each rngC In .Range("A3:A" & LRow)
If Len(rngC) = 5 And Right(rngC, 1) = "1" Then
rngC.Offset(, 1) = "x"
ElseIf Right(rngC, 1) = "A" And Int(Left(rngC, 3)) _
<> rngC.Offset(-1, 0) Then
rngC.Offset(, 1) = "x"
ElseIf Len(rngC.Offset(1, 0)) = Len(rngC) + 1 _
And InStr(rngC.Offset(1, 0), rngC) Then
rngC.Offset(, 1) = "x"
ElseIf IsNumeric(rngC) And Not _
IsNumeric(rngC.Offset(-1, 0)) Then
rngC.Offset(, 1) = "x"
End If
Next
End With

End Sub



Regards
Claus B.

 

[JeanPierre Charron]

Thank you Claus. You definitely put me on the right track.
Unfortunately I will have more alphanumeric convoluted cases that will require testing other fields to determine whether we are dealing with a lead ID or not.
I will just take one case at a time.
Have a good day,
J.P.

 

[JeanPierre Charron]

Extending my test list with real ID's resulted in new cases not resolved as expected.
I am thinking of first testing the ID length
if Len(rngC) = rngC.Offset(-1, 0) then
The serie of ifs testing, or a Select Case
If trailing Alpha ...
if trailing Numeric...
etc...
Else
The serie of ifs testing, or a Select Case
End if
Can you please give your advice ?
Thank you again.
My extended list follows
111
112
115
115A
123
250A
250B
300A1
300A2
008F009A
008F009B
011001
011002
011033
011034
0251247
025172
025174

 

[Claus Busch]

Hi J.P.,

Am Thu, 9 Oct 2014 17:55:34 -0700 (PDT) schrieb JeanPierre Charron:

Extending my test list with real ID's resulted in new cases not resolved as expected.

unfortunately you did not flag the new example. I hope I saw all cases.
If not, please post the list with the expected flags.
With different cases it is better to check the special cases first and
the simple cases at last. The length also differs in alphanumeric
values.

Sub SetFlag()
Dim LRow As Long
Dim rngC As Range
Dim flag As Boolean

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Range("B2") = "x"

For Each rngC In .Range("A3:A" & LRow)
flag = False
If Not IsNumeric(rngC) And Right(rngC, 1) = "1" Then
flag = True
ElseIf Right(rngC, 1) = "A" And Int(Left(rngC, 3)) _
<> rngC.Offset(-1, 0) Then
flag = True
ElseIf Len(rngC.Offset(1, 0)) = Len(rngC) + 1 _
And InStr(rngC.Offset(1, 0), rngC) Then
flag = True
ElseIf IsNumeric(rngC) And Not _
IsNumeric(rngC.Offset(-1, 0)) Then
flag = True
ElseIf IsNumeric(rngC) And Len(rngC) _
<> Len(rngC.Offset(-1, 0)) Then
flag = True
End If
If flag = True Then rngC.Offset(, 1) = "x"
Next
End With

End Sub


Regards
Claus B.

 

[JeanPierre Charron]

Thank you again for your logic.
Starting from a sorted list, I do not know from the start what odd sheep I will
encounter, but now you gave me some direction with the simple and the simple
and the complex ID's. The complex being varying ID length and mixed alphanumeric
The simple being Numeric ID with or without sequential break in sequence.
I will run the code with this new structure and handle any new complex ID Case as they appear.
Have a good day,
J.P.