I have two sheets. I want to match the string in cell a2 sheet 1 to the values in column A of sheet 2. here is the format of string in Sheet 1 column A: Axxxx (A - alphabet a to z, x number 0-9)

Column A sheet 2 - Axxxxxxx(A - alphabet a to z, x number 0-9)

So the purpose is 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 A010104. My data is sequential.

Hi, I think that what you are trying to do is to get the next 'number' in the sequence that you specify.

I have written a user defined function to do this as I couldn't come up with a formula. If someone else comes up with a formula you should use it.

Anyway this user defined function is used as follows:

In Cell A2 is your stem, e.g., A0101

In cell B2 is the user defined function NxtSeq, as follows:=NxtSeq(Sheet2!A2:A100,A2)

NxtSeq takes two arguments. The first is a range of cells which contain existing alphanumeric values, and the second is the cell that contains the stem - cell A2 in this case.The range for the existing alphanumeric values should be larger than the used range, so that when new values are added to the list, this formula will automatically update to show the next alphanumeric.

To create the user defined function do this:

From your worksheet, click Alt+f11 (the Alt key and function key #11 together).

This opens the Visual Basic window.

The Project explorer pane should be visible on the left - if not, from the VB menu select View - Project Explorer.

Find your workbook in the Project explorer - like this: 'VBA Project(MyWorkbook.xls)'.

Right-click it and select Insert and Module (not Class module)

Double click the new module name (Module1).In the VB window on the right enter this:

Option Explicit Public Function NxtSeq(Source As Range, Base As String) As Variant Dim dblNum As Double Dim rngCell As Range Dim blnFirstMatch As Boolean Dim rngLrgst As Range Dim n As Integer On Error GoTo ErrHnd 'set flag to base not matched blnFirstMatch = False 'loop through all cells in the range For Each rngCell In Source.Cells() 'test if value has the required stem If Left(rngCell.Text, Len(Base)) = Base Then 'if this is the first match for this base, set the Largest Range If blnFirstMatch = False Then Set rngLrgst = rngCell 'set flag to show we have an initial range blnFirstMatch = True End If 'get the number part of the text, after the base dblNum = CDbl(Right(rngCell.Text, Len(rngCell.Text) - Len(Base))) 'test if it's bigger than the first one we have If dblNum > CDbl(Right(rngLrgst.Text, _ Len(rngLrgst.Text) - Len(Base))) Then 'reset the largest range to this cell Set rngLrgst = rngCell End If End If Next rngCell 'make sure we found a match for the base - if not exit If blnFirstMatch = False Then NxtSeq = CVErr(xlErrName): Exit Function 'match found so get the largest number found dblNum = CDbl(Right(rngLrgst.Text, Len(rngLrgst.Text) - Len(Base))) 'increment by 1 dblNum = dblNum + 1 'convert back to text and add it to the base NxtSeq = Base & Format(dblNum, Application.WorksheetFunction. _ Rept("0", Len(rngLrgst.Text) - Len(Base))) Exit Function 'error handler ErrHnd: Err.Clear 'return the NA error NxtSeq.Value = CVErr(xlErrNA) End Function

Click Save from the VB menu.

Use Alt+f11 to return to the main Excel window.Enter the user-defined function with its two parameters.

If the base is not found the function returns the #NAME error.

Hope this works.

Regards

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History