CODEWITHSUNDEEP
excelvba
user3822047
user3822047

Reputation: 61

Excel VBA - how to find the next largest substring value

I have a single column range of values as part of a table. The values in this column are groups of sequential strings such as:

ABC-001
ABC-002
XYZ-001
EFDDGE-001
ABC-003
XYZ-002
ABC-004

What I need to do is assign a value in the next row that is the next value in the whatever the group is.

Example:

If the next item is an "ABC" item, I need the value in the column to be ABC-005
If the next item is a "EFDDGE" item, I need the value in the column to be EFDDGE-002 etc.

Upvotes: 0

Views: 156

Answers (2)

NinjaLlama
NinjaLlama

Reputation: 165

Here is a little sub I threw together to hopefully get you pointed in the right direction. There are a lot of improvements that could be made to the following code but it is functional. I am assuming that you know how to add and run subroutines. If you need any clarification please let me know.

image: sample data and output

code:

Option Explicit

' This sub will get generate the next integer value for all of the unique prefixes provided in column A
'   desired input format is xxxx-xxxx
Sub getNextValue()

    Dim lastRow As Long

    With Sheets("Sheet1")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With

    Dim arrayPrefix() As String
    Dim arrayMax() As String
    Dim i As Integer
    Dim iData As String
    Dim prefixData As String
    Dim maxData As String
    Dim test As String
    Dim index As String

    ReDim arrayPrefix(0)
    ReDim arrayMax(0)

    For i = 1 To lastRow

        iData = Cells(i, 1).Value
        prefixData = Split(iData, "-")(0)
        maxData = Split(iData, "-")(1)

        If CheckInArray(prefixData, arrayPrefix) Then

            index = Application.Match(prefixData, arrayPrefix, False) - 1

            ' keeps the maximum value encountered so far
            If maxData > arrayMax(index) Then

                arrayMax(index) = maxData

            End If


        Else

            ' resizes the matricies to hold more if needed.
            ReDim Preserve arrayPrefix(UBound(arrayPrefix) + 1)
            ReDim Preserve arrayMax(UBound(arrayMax) + 1)
            arrayPrefix(UBound(arrayPrefix)) = prefixData
            arrayMax(UBound(arrayMax)) = maxData

        End If

    Next i


    ' Output next values to adjacent columns
    For i = 1 To UBound(arrayPrefix)

        Cells(i, 3).Value = arrayPrefix(i)
        Cells(i, 4).Value = arrayMax(i) + 1

    Next i


End Sub


Function CheckInArray(stringToBeFound As String, arr As Variant) As Boolean

    Dim i As Integer

    For i = 0 To UBound(arr)

        If arr(i) = stringToBeFound Then

            CheckInArray = True
            Exit Function

        End If


    Next i

  CheckInArray = False

End Function

Upvotes: 0

Netloh
Netloh

Reputation: 4378

You could use a formula like this

=LEFT(A1,FIND("-",A1)-1)&"-"&RIGHT("00"&RIGHT(A1,LEN(A1)-FIND("-",A1))+1,3)

This will, however, only work as long as the number indexation is restricted to 3 digits.

Upvotes: 1

Related Questions