Excel - Match a string to a column & increment by 1

March 2017




Issue


I have two sheets with data as follows:

Sheet :

String, result
A0101

Format of string in Sheet 1 column A: Axxxx (A - alphabet a to z, x number 0-9). the Alphabet is the series starter.

Sheet 2:

A0101001 
B0102001 
Q9911001 
S7834001 
A0101002 
A0101003 


Column A sheet 2 - Axxxxxxx(A - alphabet a to z, x number 0-9)
the last three numbers are in sequence for each of the first five charcters.

as in the data above:
A0101001 
A0101002 
A0101003

The number will never jump to 005 for the string A0101

I want to match the string in cell A2 sheet 1 to the values in column A of sheet 2.
If my string is : A0101 from sheet 1 and I find the following three entries from sheet 2: A0101001, A0101002, A0101003, the result in cell B2 of sheet 1 is A0101004. My data is sequential.

Solution


I have added one more data in A3 in sheet 1
B0102
In sheet 2 row is column headings and data is below
string:

A0101001 
B0102001 
Q9911001 
S7834001 
A0101002 
A0101003 


Then try this macro"test". If you want to recheck first run mcaro "undo" and then "test"


Sub test()
Dim r As Range, c As Range, x As String
Dim j As Integer, cfind As Range
Dim y As String, add As String
j = 0
With Worksheets("sheet1")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
'msgbox  r.Address
For Each c In r
x = c.Value
With Worksheets("sheet2")
Set cfind = .Cells.Find(what:=x, lookat:=xlPart)
If cfind Is Nothing Then GoTo nnext

j = j + 1
add = cfind.Address
'msgbox  j
'msgbox  add
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then GoTo nnext
If cfind.Address = add Then GoTo line1
'msgbox  cfind.Address
j = j + 1
'msgbox  j
Loop
line1:
y = Mid(cfind, 6, 2)
End With 'sheet2
c.Offset(0, 1) = x & y & j + 1

nnext:
j = 0
Next c

End With

End Sub



Sub undo()
With Worksheets("sheet1")
Range(.Range("B2"), .Range("B2").End(xlDown)).Clear
End With
End Sub

Note


Thanks to venkat1926 for this tip.

Related


Published by aakai1056.
This document, titled "Excel - Match a string to a column & increment by 1," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).