"Looking up" based on concatenation of values, some of which fall within a range of x to y

[mzza]

Hi,

I'm not convinced there's a way of doing this without using VBA which I would really like to avoid, but here goes...

I have been tasked with matching 2 spreadsheets from entirely different sources. Each is a list of distinct vehicles with a unique identifier (which is proprietary to each source, so the IDs are different although the vehicles are the same).

To try and match vehicles my first line of thought was to concatenate data to create a string of (for example) [Manufacturer]+[Model]+[Year]+[Engine CC]+etc, then do a Vlookup based on this concatenated string to find the matching vehicle in the other spreadsheet (and thereby build a list of 'Unique ID A' matched to 'Unique ID B', the ultimate aim of the exercise).

The problem is, owing to slight variances in how such things are measured, the (e.g.) Engine CC may be slightly different in each spreadsheet for the exact same vehicle. So the "vlookup" (or equivalent jury-rigged operation) needs to allow for a tolerance of +/-5 on the Engine CC to still count as a potential "match". As an example, I would need the (Ford+Mustang+1994+1799CC) to match to the (Ford+Mustang+1994+1803CC); however, that's just accounting for 4 variables, one of which has a tolerance of +/-5; there are many more variables I need to match on, and 7 of those variables have tolerances rather than exact values.

I've been wracking my brains but cannot fathom an efficient/elegant way of doing this... or even an inelegant one that at least does the job!

As I say, I would very much prefer to avoid coding if possible, but beggars can't be choosers to if that's the only realistic solution I'm open to all suggestions.

TIA for any help anyone can provide.

 

[Swayzy]

Do you just need to check that a vechicle is present in both sheets or do you need to find the actual matching rows? Do you need to do this often or is a One time thing? Maybe countifs would work to just check the presence? Then you can use intervalls or exact values to check against and of everything is ok you get a 1.

Overall this is a case for VBA, you can still setup the tolerances for parameters in cells to check against so that the macro is easily adaptable.

 

[mzza]

I need to find the actual matching rows (so Unique ID XYZ from Sheet 1 correlates to Unique ID 123 from Sheet 2). It's a one time thing, but countifs won't do it because it has to be [Field1]+[Field2]+[Field3]+... matches to the same exact fields in the other sheet (+/- variance for certain fields)...

Like I say I'm willing to countenance VBA if it's that or nothing...

 

[Swayzy]

Countifs could find a match with any type or number of search criteria, you could even find the matching row One by One vechicle but then it would be tedious.

VBA Will be the quickest way to get a reliable solution. Just a simple looping macro that checks each field vs field with any upper and lower boundaries. That then prints the corresponding row value. It is especially good to use VBA when it is only you who need to use it, VBA is a great tool once you master even som basic stuff like this. Especially matching and extracting data out of compliated data sets is much quicker since you can get it to exactly what you need without any voodoo formulas you dont really trust

 

[mzza]

Yeah I should probably bite the bullet and just get back into code-writing (I haven't done so in over a decade and was a rank beginner even then).

Do you have an example I could crib from?

Say that in both sheets, Column A is Manufacturer, B is Make, C is Engine Size (variable by +/-5)? I can probably then extrapolate from this... would really appreciate someone smarter than me to get me started!

 

[Swayzy]

OBS!! Not a finished solution. So I've cobbled this together with some stuff I've used for other types of itteration problems. Basically you have a third master sheet that you use to set criterias. If you have coded some before I would expect you can maybe finish it yourself? The base is there it just needs some work on the overall looping of rows and where to put the row number when it finds a match for all criterias.

The idea is that you make a support column that concatenate all criteria with a / allas =A1&"/"&A2 etc. OBS! You will need the new columns to have the same number of fields and in the same order. When it finds a numerical value in the base sheet cell value it will look in the setup sheet where you need to the percent range the value should fall inside. For example in A5 you put Col 3, B5 95% and C5 105%. The Col X figure have to correspond to the criteria before the x'th / in the new concatenated ID.

The power of macros is that when you are used to the below koncepts and can use them by your self you can simplify the macro abit and then you can write it up really fast and hard code some input variables directly and be done before you even have gotten a reply in a forum thread

Sub search()Application.ScreenUpdating = False
Dim shB As Worksheet
Dim shM As Worksheet


sht1 = Cells(1, 2)
sht2 = Cells(1, 3)


Set shB = Worksheets(sht1)
Set shM = Worksheets(sht2)






rst = Cells(2, 2)
rend = Cells(2, 3)
r1 = rst
r2 = rend


c1 = Cells(3, 2)
c2 = Cells(3, 3)
mtch2 = c2 - c1




For r1 = rst To rend


        Count = rst
        Count2 = rend


Do Until Count = Count2


        mtch1 = shB.Cells(r1, c1)
        If Right(mtch1, 1) <> "/" Then mtch1 = mtch1 & "/"
        Countmtch1 = Len(mtch1) - Len(Replace(mtch1, "/", ""))
        
        mtch3 = shM.Cells(Count, c1)
        If Right(mtch3, 1) <> "/" Then mtch3 = mtch3 & "/"
        Countmtch3 = Len(mtch3) - Len(Replace(mtch3, "/", ""))
        col = 1
        
Do Until Countmtch1 = 0
 
If mtch1 = "" Then
Else
    mtch2 = Left(mtch1, InStr(mtch1, "/") - 1)
    x = Len(mtch2) + 1
    mtch1 = Right(mtch1, Len(mtch1) - x)
    
    
    mtch4 = Left(mtch3, InStr(mtch3, "/") - 1)
    y = Len(mtch3) + 1
    mtch3 = Right(mtch3, Len(mtch3) - x)
    
    
    
    If IsNumeric(mtch2) = "TRUE" Then


    valrng = WorksheetFunction.Match("Col " & col, Range("A:A"), 0)
        
        dw1 = mtch2 * Cells(valrng, 2)
        up1 = mtch2 * Cells(valrng, 3)
        
        mtch4 = mtch4 * 1
        If mtch4 >= dw1 And mtch4 <= up1 Then
        mtch2 = mtch4
        End If
        
    Else
       
        mtch2 = LCase(mtch2)
        mtch2 = Replace(mtch2, " ", "")
     
        mtch4 = LCase(mtch4)
        mtch4 = Replace(mtch4, " ", "")


    End If
    
    If mtch2 = mtch4 Then
    mtch = mtch + 1
    End If
    
    
End If
 col = col + 1
Countmtch1 = Countmtch1 - 1
Loop




If mtch = Countmtch3 Then






End If




Loop


Next r1








Application.ScreenUpdating = True
End Sub

 

[mzza]

If you have coded some before I would expect you can maybe finish it yourself? The base is there it just needs some work on the overall looping of rows and where to put the row number when it finds a match for all criterias.

Alas, you overestimate my skill lol. I'm afraid this is a little beyond me, can anyone help? Let's say, for the sake of argument, there are 10 values to match on, first 5 are fixed, last five are variances... all in columns B to K. Does this make it easier to create a complete solution? Apologies, I thought I could maybe have a go at it but looking at the above post it turns out I'm really not enough of a VBA-er to extrapolate from partial code...

 

[Swayzy]

I can try to finish it over the weekend! What is also good with learning some basic code is that you can very easily re-use code snippets which is very nice

 

[Swayzy]

Alas, you overestimate my skill lol. I'm afraid this is a little beyond me, can anyone help? Let's say, for the sake of argument, there are 10 values to match on, first 5 are fixed, last five are variances... all in columns B to K. Does this make it easier to create a complete solution? Apologies, I thought I could maybe have a go at it but looking at the above post it turns out I'm really not enough of a VBA-er to extrapolate from partial code...

So since I didnt know exactly where you want the output this became a pretty general solution that can be applied to what ever data. What needs to be added is two new columns (one for each sheet/data table) were you concat all the criterias with a /. Hope you understand the instructions, otherwise just ask and I will clarify.

OBS!! The code will remove the concatenated column values in the match sheet as it goes.

Pictured is my example of Sheet4, refered to as master sheet.

Sub search2()

Dim shB As Worksheet
Dim shM As Worksheet


sht1 = Cells(1, 2)
sht2 = Cells(1, 3)


Set shB = Worksheets(sht1)
Set shM = Worksheets(sht2)






rst = Cells(2, 2)
rend = Cells(2, 3)
r1 = rst
r2 = rend


c1 = Cells(3, 2)
c2 = Cells(3, 3)
c3 = Cells(4, 2)
c4 = Cells(4, 4)




Do Until r1 = r2
mtch = 0
Countmtch3 = 1




            Count = rst
            Count2 = rend
    
        Do Until Count = Count2 Or mtch = Countmtch3
        mtch = 0
                mtch1 = shB.Cells(r1, c1)
                If Right(mtch1, 1) <> "/" Then mtch1 = mtch1 & "/"
                Countmtch1 = Len(mtch1) - Len(Replace(mtch1, "/", ""))
                
                mtch3 = shM.Cells(Count, c2)
                If Right(mtch3, 1) <> "/" Then mtch3 = mtch3 & "/"
                Countmtch3 = Len(mtch3) - Len(Replace(mtch3, "/", ""))
                If Len(mtch1) = 1 Then Countmtch3 = 2
                col = 1
                
             If Countmtch1 = Countmtch3 Then
        Do Until Countmtch1 = 0
         
        If Len(mtch3) = 1 Then
        Else
            mtch2 = Left(mtch1, InStr(mtch1, "/") - 1)
            x = Len(mtch2) + 1
            mtch1 = Right(mtch1, Len(mtch1) - x)
            
            mtch4 = Left(mtch3, InStr(mtch3, "/") - 1)
            y = Len(mtch4) + 1
            mtch3 = Right(mtch3, Len(mtch3) - y)
            
            
                    If IsNumeric(mtch2) = "TRUE" Then
                
                        valrng = WorksheetFunction.Match("Col " & col, Range("A:A"), 0)
                        
                        dw1 = mtch2 * Cells(valrng, 2)
                        up1 = mtch2 * Cells(valrng, 3)
                        
                        mtch4 = mtch4 * 1
                        If mtch4 >= dw1 And mtch4 <= up1 Then
                        mtch2 = mtch4
                        End If
                        
                    Else
                       
                        mtch2 = LCase(mtch2)
                        mtch2 = Replace(mtch2, " ", "")
                     
                        mtch4 = LCase(mtch4)
                        mtch4 = Replace(mtch4, " ", "")
                
                    End If
            
            If mtch2 = mtch4 Then
            mtch = mtch + 1
            End If
            
            
        End If
         col = col + 1
        Countmtch1 = Countmtch1 - 1
        Loop
    
    
    If mtch = Countmtch3 Then
    
    'Keep this line to punch out row number in master sheet
    Cells(r1, c4) = Count
    'Keep this line to punch out row number in base sheet
    shB.Cells(r1, c3) = Count
    shM.Cells(Count, c2) = ""
    End If
    
    End If
    Count = Count + 1
    Loop




r1 = r1 + 1
Loop


End Sub