vlookup with Wildcard(s) in data table Excel 2010

[Sparty7]

Hi All,

First time, long time here and looking for a little specialized help. I am looking to do a cross system data validation template and each have unique and very different account codes that I need to cross reference. The biggest issue I am running into is that there is at least one wildcard, sometimes two, in the table that my vlookup needs to reference and therefore is beyond my abilities to figure out. Currently I have my workbook set up with 3 sheets - 1st is system data "A", 2nd is system data "B" and the third is the mapping sequence:

Sheet 1)
[TABLE="width: 141"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Account[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]01A[/TD]
[TD="align: center"]5000[/TD]
[/TR]
[TR]
[TD="align: center"]05B[/TD]
[TD="align: center"]2500[/TD]
[/TR]
[TR]
[TD="align: center"]16A02[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]30G10[/TD]
[TD="align: center"]2000[/TD]
[/TR]
[TR]
[TD="align: center"]30G10OTH[/TD]
[TD="align: center"]7000[/TD]
[/TR]
[TR]
[TD="align: center"]30G10CASH[/TD]
[TD="align: center"]450

[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2)
[TABLE="width: 153"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: center"]Account[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]110004[/TD]
[TD="align: center"]1000[/TD]
[/TR]
[TR]
[TD="align: center"]110007[/TD]
[TD="align: center"]4000[/TD]
[/TR]
[TR]
[TD="align: center"]120008-009[/TD]
[TD="align: center"]1000[/TD]
[/TR]
[TR]
[TD="align: center"]12000201[/TD]
[TD="align: center"]1500[/TD]
[/TR]
[TR]
[TD="align: center"]130102[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]130129[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]14000010[/TD]
[TD="align: center"]1000[/TD]
[/TR]
[TR]
[TD="align: center"]140008[/TD]
[TD="align: center"]500[/TD]
[/TR]
[TR]
[TD="align: center"]140009[/TD]
[TD="align: center"]500[/TD]
[/TR]
[TR]
[TD="align: center"]140010-001[/TD]
[TD="align: center"]4000[/TD]
[/TR]
[TR]
[TD="align: center"]140008-001[/TD]
[TD="align: center"]2000[/TD]
[/TR]
[TR]
[TD="align: center"]140009-001[/TD]
[TD="align: center"]500[/TD]
[/TR]
[TR]
[TD="align: center"]14000001-001[/TD]
[TD="align: center"]500[/TD]
[/TR]
[TR]
[TD="align: center"]140020-002[/TD]
[TD="align: center"]250[/TD]
[/TR]
[TR]
[TD="align: center"]140029-002[/TD]
[TD="align: center"]200[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 3)
[TABLE="width: 166"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Account[/TD]
[TD="align: center"]Account[/TD]
[/TR]
[TR]
[TD="align: center"]01A[/TD]
[TD="align: center"]11000*[/TD]
[/TR]
[TR]
[TD="align: center"]05B[/TD]
[TD="align: center"]12000*[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]12000*-00*[/TD]
[/TR]
[TR]
[TD="align: center"]16A02[/TD]
[TD="align: center"]13010*[/TD]
[/TR]
[TR]
[TD="align: center"]30G10[/TD]
[TD="align: center"]1400001*[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]14000*[/TD]
[/TR]
[TR]
[TD="align: center"]30G10OTH[/TD]
[TD="align: center"]1400001*-001[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]14000*-001[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]14000*[/TD]
[/TR]
[TR]
[TD="align: center"]30G10CASH[/TD]
[TD="align: center"]14002*-002[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Any insight would be greatly appreciated as I am stumped! Thanks in advance!

 

[tmnt43]

I'm fairly certain this can't be done with 1 formula, though people tend to amaze with what they can do with formulas. The wildcards in multiple positions is really the dealbreaker here, as far as I'm aware.

A solution would be possible with VBA - not sure if that's an option for you though. What's the desired output?

 

[Sparty7]

I would have to agree that this probably cannot be done with just 1 formula though I have come close with =iferrors( and nested vlookups, but that 2nd wildcard has been my Kryptonite.

Just an update I actually have the columns flipped in my mapping sheet #3 so the column with the wildcards is in column 1 and the alpha numeric accounts are in column 2.

I am not opposed to using VBA, but am very rusty. Basically what I need to do is use the mapping sequence to pull the accounts from sheet 1 to match what I have in sheet 2. Should look something like so for sheet 2:

[TABLE="width: 254"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Account[/TD]
[TD]Balance[/TD]
[TD]Formula Result[/TD]
[/TR]
[TR]
[TD]110004[/TD]
[TD]1000[/TD]
[TD]01A[/TD]
[/TR]
[TR]
[TD]110007[/TD]
[TD]4000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]120008[/TD]
[TD]1000[/TD]
[TD]05B[/TD]
[/TR]
[TR]
[TD]12000201[/TD]
[TD]1500[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]130102[/TD]
[TD]0[/TD]
[TD]16A02[/TD]
[/TR]
[TR]
[TD]130129[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14000010[/TD]
[TD]1000[/TD]
[TD]30G10[/TD]
[/TR]
[TR]
[TD]140008[/TD]
[TD]500[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]140009[/TD]
[TD]500[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14000010-001[/TD]
[TD]4000[/TD]
[TD]30G10OTH[/TD]
[/TR]
[TR]
[TD]140008-001[/TD]
[TD]2000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]140009-001[/TD]
[TD]500[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14000001-002[/TD]
[TD]500[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]130102-001[/TD]
[TD]250[/TD]
[TD]30G10CASH[/TD]
[/TR]
[TR]
[TD]130111-001[/TD]
[TD]200[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I then used a pivot table to create a way to use a vlookup and validate that the sheet 1 balances = sheet 2 balances.

 

[tmnt43]

Well, wrote some code that sort of works. I realized at the end that your wildcards are actually pretty generic. To a computer, 1400001* and 1400001*-001 could be the same value.

Try this code in a copy of your workbook, but basically, without a bunch of custom handling, this may not be that simple

Sub tester1()


Dim accountWs As Worksheet, matchWs As Worksheet
Dim accountWsRc As Double, matchWsRc As Double
Dim i As Double, j As Double
Dim accountId As String


Set accountWs = Sheets("Sheet3")
Set matchWs = Sheets("Sheet2")


'get last row of accountWs and matchWs
accountWsRc = accountWs.Cells(matchWs.Rows.Count, 1).End(xlUp).Row
matchWsRc = matchWs.Cells(matchWs.Rows.Count, 1).End(xlUp).Row


startloop = 2 'assuming data starts in row 2


For i = startloop To accountWsRc


    accountId = accountWs.Cells(i, 2).Value
    replaceId = accountWs.Cells(i, 1).Value
    
    For j = startloop To matchWsRc
        
        If Right(accountId, 1) = "*" And matchWs.Cells(j, 3).Value = "" Then
            If matchWs.Cells(j, 1).Value Like accountId Or matchWs.Cells(j, 1).Value = accountId Then
                matchWs.Cells(j, 3).Value = replaceId
            End If
        Else
            If matchWs.Cells(j, 1).Value Like accountId Or matchWs.Cells(j, 1).Value = accountId Then
                matchWs.Cells(j, 3).Value = replaceId
            End If
        End If
        
    Next j
    
Next i


End Sub

 

[Sparty7]

Thanks for the help. After playing around with it a but I think I was able to tailor it to fit my needs. I appreciate the help.