0
Thanks

A few words of thanks would be greatly appreciated.

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

## 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```

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))
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
'msgbox  j
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then GoTo nnext
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.
0
Thanks

A few words of thanks would be greatly appreciated.

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

## Related

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

Recommended

DON'T MISS

TRENDING GAMES & APPS
• Professional

• Internet

• Internet

• Professional

• Internet

• Internet

• Video games

• Internet

• Video games

• Professional

• Video games