Excel - Match a string to a column & increment by 1
On April 07,2022 by Tom Routley
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.
Leave a Reply