Comparing 2 sets of data for partial match

Anika

New Member
Joined
Aug 22, 2014
Messages
2
I am trying to streamline a manual process where we receive this data from the customer:

Item amount style
12345 10.00 13
12346 15.00 14
13247 20.00

and have to find the best match among our offered items:

Item Price style
45612345 10.45 13
45612345 10.45 25
45612346 15.99 14
45612347 21.00 15
45612347 27.00 17
45612347 35.00 45

Ideally I'd like to have something that returns the "best match" based on a comparison of either partial or transposed item #, amount +/- $5.00 and style on the right side on the customer's data. Is this possible?

The expected best match would look like this:

Item price style
45612345 10.45 13
45612346 15.99 14
45612347 21.00 15

We spend a lot of time researching this manually and anything you can offer would be greatly appreciated.

Thank you
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Tomorrow night when I get off work, I can create a macro that will do that. Sorry but I don't have time to do it tonight because it will take me like an hour. Before I start, I will need some more information. Please provide me with a table with Columns and Rows and your dataset as it appears in your excel file.
Example:
AB
1datadata
2datadata

<tbody>
</tbody>
To create a table like that in the forum, click on advanced. Then there is an insert table button. Make sure to change the "Table Style" to "Full Grid" in the table properties. If you don't, the table will look like this...
AB
1datadata
2datadata

<tbody>
</tbody>


Also, I noticed that when a user enters the criteria to search for, you left out "style" in one of your examples. I'm assuming that this implies that if it is blank, all styles are okay to search for. What if someone leaves out item number, what do you want it to do. What if someone leaves out price, what do you want it to do? What if they leave out 2 criteria like item number and style?

What if there are multiple matching criteria for the first search the user inputted? Should it display all of them or just one? Where should it display?

Finally, do you want it to give you the results at the push of a button or would you like it to give you the results when someone enters the search parameters into the cells and presses enter( on worksheet change event)?
 
Upvote 0
Tomorrow night when I get off work, I can create a macro that will do that. Sorry but I don't have time to do it tonight because it will take me like an hour. Before I start, I will need some more information. Please provide me with a table with Columns and Rows and your dataset as it appears in your excel file.
Example:
AB
1datadata
2datadata

<tbody>
</tbody>
To create a table like that in the forum, click on advanced. Then there is an insert table button. Make sure to change the "Table Style" to "Full Grid" in the table properties. If you don't, the table will look like this...
AB
1datadata
2datadata

<tbody>
</tbody>


Also, I noticed that when a user enters the criteria to search for, you left out "style" in one of your examples. I'm assuming that this implies that if it is blank, all styles are okay to search for. What if someone leaves out item number, what do you want it to do. What if someone leaves out price, what do you want it to do? What if they leave out 2 criteria like item number and style?

What if there are multiple matching criteria for the first search the user inputted? Should it display all of them or just one? Where should it display?

Finally, do you want it to give you the results at the push of a button or would you like it to give you the results when someone enters the search parameters into the cells and presses enter( on worksheet change event)?

Hi, thank you for your reply.

Here is a sample user data table:

ItemAmountStyle
234567810.0012
12347820.0015
1234567825.0015
12345678112.0012
12.0012
1234567815.00

<tbody>
</tbody>

Here is a sample of actual inventory:

ItemPriceStyle
1234567810.4512
1234567820.0015
1234567926.0015
1234567912.0012
1234567815.0013

<tbody>
</tbody>

In the event where an item is left blank, it is ok to search for all possible matches from the other 2 available options. There will always be 2 of the 3 available, if not, it can return a string "Not enough data to determine" or something like that. If someone leaves out the Style, search by item number and price +/- 5.00, if someone leaves out the item # search for style and price +/_ 5.00. Amount should never be left out, but the same logic can apply to that, all matches for that item # in the style provided.

In the event that more than one match is possible, it should return all possible matches to the right of the user data. Say user data occupies A1:A3, the first match should occupy A5:A7, next should occupy A9:A11, basically leaving one empty column between matches for ease of viewing.

The current inventory resides in a spreadsheet, I envision entering the data in tab2 of that same spreadsheet and the search should find a match automatically (on worksheet change event) as we would most likely just copy and paste in the customer input.

I tried doing something with vlookups and this is sadly beyond my ability. The transposed digits of item number and if there is a missing digit in the middle of the range (ie 123567 instead of 1234567) completely stumped me.

Please let me know if I can clarify further and thank you again.
 
Upvote 0
Okay so make sure to read all the comments in the code. Especially the comment I put in all capital letters. That comment is related to your desire for wanting 123567 to be the search string and 1234567 to be in your inventory, and consider them a Match. That code will eventually go there. I was unable to figure out a way to do that though. Mainly because I don't know how to combine the LIKE function I already have, with a Do Until loop that counts how many differences there are between the 2 strings. Anyways here is the working code.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'change "Sheet1" to the name of your first sheet.
    wkshtSearch = "Sheet1"
    'change "Sheet2" to the name of your second sheet.
    wkshtInventory = "Sheet2"
    Sheets(wkshtSearch).Columns("E:ZZ").EntireColumn.ClearContents
    rowNum = 2
    'Do Until:  This will contine to run the code until the first blank A, B, and C row in the search worksheet.
    Do Until Sheets(wkshtSearch).Range("A" & rowNum).Value = "" _
        And Sheets(wkshtSearch).Range("B" & rowNum).Value = "" _
        And Sheets(wkshtSearch).Range("C" & rowNum).Value = ""
        'IF at least two search criteria have been entered, run this code.
        If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
            And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
            Or Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
            And Sheets(wkshtSearch).Range("C" & rowNum).Value <> "" Then
                endOfInventoryList = 2
                aSearch = Sheets(wkshtSearch).Range("A" & rowNum).Value
                bSearchHigh = Sheets(wkshtSearch).Range("B" & rowNum).Value + 5
                bSearchLow = Sheets(wkshtSearch).Range("B" & rowNum).Value - 5
                cSearch = Sheets(wkshtSearch).Range("C" & rowNum).Value
                hResults = 69
                h = Chr(hResults)
                'Do Until:  This will continue to run the code until the first blank A, B, and C row in the inventory worksheet.
                Do Until Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value = "" _
                    And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value = "" _
                    And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = ""
                    secondIfBool = True
                    'LATER I WANT TO MODIFY THIS IF STATEMENT TO ALSO LOOK FOR EXAMPLE 123567 FROM 1234567
                    'IF Item and Amount is not blank, then run this code.
                    If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
                        And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" Then
                            If Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value Like "*" & aSearch & "*" _
                                And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                                And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow Then
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                    hResults = hResults + 1
                                    h = Chr(hResults)
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                    hResults = hResults + 1
                                    h = Chr(hResults)
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                    hResults = hResults + 2
                                    h = Chr(hResults)
                                    secondIfBool = False
                            End If
                    Else
                        'IF Amount and Style is not blank, and it hasn't already been displayed from the previous IF statement, then run this code.
                        If secondIfBool = True _
                            And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                            And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow _
                            And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = cSearch Then
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                hResults = hResults + 1
                                h = Chr(hResults)
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                hResults = hResults + 1
                                h = Chr(hResults)
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                hResults = hResults + 2
                                h = Chr(hResults)
                        End If
                    End If
                    endOfInventoryList = endOfInventoryList + 1
                Loop
        Else
            If Sheets(wkshtSearch).Range("B" & rowNum).Value = "" Then
                Sheets(wkshtSearch).Range("E" & rowNum).Value = "Enter an Amount"
            End If
        End If
        rowNum = rowNum + 1
    Loop
End Sub
 
Last edited:
Upvote 0
PPlay around with your dataset using this code and find out what isn't performing exactly the way you want. I'll modify it
 
Upvote 0
I have good news. I have solved that part of the problem when users input something like 123567 when the inventory says 1234567. The problem is that it is extremely long and complicated code. I already tested it and it works. I now need to implement it to the code I already gave you. I didn't design this new code in conjunction with the old code because I wanted to keep it simple while I evaluated to see if it was even possible. Well it is possible, but it's super difficult to accomplish. I'm so tired right now that if I try to implement the two codes together, I will mess it up. I'll do it tomorrow night. In the mean time, let me know what you think of the code I already gave you.
 
Upvote 0
I haven't forgotten about you. I'm still working on solving your issue. I finished implementing the new code with the old code. The problem is that I made a mistake in the new code. The output is not working correctly and I will need to inspect it line by line. Hopefully tomorrow night I'll be finished. This project is definitely the hardest I've ever done.
 
Upvote 0
SOLVED
Here you go bud. All done. I had to start from scratch 3 times to get it right.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'change "Sheet1" to the name of your first sheet.
    wkshtSearch = "Sheet1"
    'change "Sheet2" to the name of your second sheet.
    wkshtInventory = "Sheet2"
    Sheets(wkshtSearch).Columns("E:ZZ").EntireColumn.ClearContents
    rowNum = 2
    'Do Until:  This will contine to run the code until the first blank A, B, and C row in the search worksheet.
    Do Until Sheets(wkshtSearch).Range("A" & rowNum).Value = "" _
        And Sheets(wkshtSearch).Range("B" & rowNum).Value = "" _
        And Sheets(wkshtSearch).Range("C" & rowNum).Value = ""
        'IF at least two search criteria have been entered, run this code.
        If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
            And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
            Or Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
            And Sheets(wkshtSearch).Range("C" & rowNum).Value <> "" Then
                endOfInventoryList = 2
                aSearch = Sheets(wkshtSearch).Range("A" & rowNum).Value
                bSearchHigh = Sheets(wkshtSearch).Range("B" & rowNum).Value + 5
                bSearchLow = Sheets(wkshtSearch).Range("B" & rowNum).Value - 5
                cSearch = Sheets(wkshtSearch).Range("C" & rowNum).Value
                hResults = 69
                h = Chr(hResults)
                'Do Until:  This will continue to run the code until the first blank A, B, and C row in the inventory worksheet.
                Do Until Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value = "" _
                    And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value = "" _
                    And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = ""
                    secondIfBool = True
                    'IF Item and Amount is not blank, then run this code.
                    If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
                        And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" Then
                            If Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value Like "*" & aSearch & "*" _
                                And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                                And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow Then
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                    hResults = hResults + 1
                                    h = Chr(hResults)
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                    hResults = hResults + 1
                                    h = Chr(hResults)
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                    hResults = hResults + 2
                                    h = Chr(hResults)
                                    secondIfBool = False
                            End If
                    'IF Item and Amount is not blank, but didn't find a match using the previous search method in the last IF statement, then run this code.
                            If secondIfBool = True Then
                                output = temp(Sheets(wkshtSearch).Range("A" & rowNum).Value, Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value)
                                If output = True _
                                    And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                                    And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow Then
                                        Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                        hResults = hResults + 1
                                        h = Chr(hResults)
                                        Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                        hResults = hResults + 1
                                        h = Chr(hResults)
                                        Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                        hResults = hResults + 2
                                        h = Chr(hResults)
                                End If
                            End If
                    Else
                        'IF Amount and Style is not blank, and it hasn't already been displayed from the previous IF statement, then run this code.
                        If secondIfBool = True _
                            And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                            And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow _
                            And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = cSearch Then
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                hResults = hResults + 1
                                h = Chr(hResults)
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                hResults = hResults + 1
                                h = Chr(hResults)
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                hResults = hResults + 2
                                h = Chr(hResults)
                        End If
                    End If
                    endOfInventoryList = endOfInventoryList + 1
                Loop
        Else
            If Sheets(wkshtSearch).Range("B" & rowNum).Value = "" Then
                Sheets(wkshtSearch).Range("E" & rowNum).Value = "Enter an Amount"
            End If
        End If
        rowNum = rowNum + 1
    Loop
End Sub

Function temp(a, b)
    'THIS CODE DOES A PARTIAL VLOOKUP SO THAT IF THERE IS ONLY 1 DIFFERENCE BETWEEN THE TWO VALUES, THEN IT WILL COUNT AS A MATCH.
    cnt1 = 1
    errorCount = 0
    'Do Until: Find the first character of a within b.
    Do Until Mid(a, 1, 1) = Mid(b, cnt1, 1) Or cnt1 > Len(b)
        cnt1 = cnt1 + 1
    Loop
    If cnt1 > Len(b) Then
        errorCount = errorCount + 1
    End If
    cnt2 = 2
    Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
        cnt2 = cnt2 + 1
    Loop
    If cnt2 < Len(a) Then
        errorCount = errorCount + 1
    End If
    If errorCount < 2 And cnt2 < Len(a) Then
        cnt2 = cnt2 + 1
        Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
            cnt1 = cnt1 + 1
            cnt2 = cnt2 + 1
        Loop
    End If
    If cnt2 - 1 < Len(a) Then
        errorCount = errorCount + 1
        cnt2 = cnt2 + 1
        Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
            cnt1 = cnt1 + 1
            cnt2 = cnt2 + 1
        Loop
    End If
    If cnt2 < Len(a) Then
        errorCount = errorCount + 1
    End If
    If errorCount < 2 Then
        temp = True
    Else
        temp = False
    End If
End Function
 
Upvote 0
SOLVED
Here you go bud. All done. I had to start from scratch 3 times to get it right.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'change "Sheet1" to the name of your first sheet.
    wkshtSearch = "Sheet1"
    'change "Sheet2" to the name of your second sheet.
    wkshtInventory = "Sheet2"
    Sheets(wkshtSearch).Columns("E:ZZ").EntireColumn.ClearContents
    rowNum = 2
    'Do Until:  This will contine to run the code until the first blank A, B, and C row in the search worksheet.
    Do Until Sheets(wkshtSearch).Range("A" & rowNum).Value = "" _
        And Sheets(wkshtSearch).Range("B" & rowNum).Value = "" _
        And Sheets(wkshtSearch).Range("C" & rowNum).Value = ""
        'IF at least two search criteria have been entered, run this code.
        If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
            And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
            Or Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
            And Sheets(wkshtSearch).Range("C" & rowNum).Value <> "" Then
                endOfInventoryList = 2
                aSearch = Sheets(wkshtSearch).Range("A" & rowNum).Value
                bSearchHigh = Sheets(wkshtSearch).Range("B" & rowNum).Value + 5
                bSearchLow = Sheets(wkshtSearch).Range("B" & rowNum).Value - 5
                cSearch = Sheets(wkshtSearch).Range("C" & rowNum).Value
                hResults = 69
                h = Chr(hResults)
                'Do Until:  This will continue to run the code until the first blank A, B, and C row in the inventory worksheet.
                Do Until Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value = "" _
                    And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value = "" _
                    And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = ""
                    secondIfBool = True
                    'IF Item and Amount is not blank, then run this code.
                    If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
                        And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" Then
                            If Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value Like "*" & aSearch & "*" _
                                And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                                And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow Then
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                    hResults = hResults + 1
                                    h = Chr(hResults)
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                    hResults = hResults + 1
                                    h = Chr(hResults)
                                    Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                    hResults = hResults + 2
                                    h = Chr(hResults)
                                    secondIfBool = False
                            End If
                    'IF Item and Amount is not blank, but didn't find a match using the previous search method in the last IF statement, then run this code.
                            If secondIfBool = True Then
                                output = temp(Sheets(wkshtSearch).Range("A" & rowNum).Value, Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value)
                                If output = True _
                                    And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                                    And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow Then
                                        Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                        hResults = hResults + 1
                                        h = Chr(hResults)
                                        Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                        hResults = hResults + 1
                                        h = Chr(hResults)
                                        Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                        hResults = hResults + 2
                                        h = Chr(hResults)
                                End If
                            End If
                    Else
                        'IF Amount and Style is not blank, and it hasn't already been displayed from the previous IF statement, then run this code.
                        If secondIfBool = True _
                            And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
                            And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow _
                            And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = cSearch Then
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
                                hResults = hResults + 1
                                h = Chr(hResults)
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
                                hResults = hResults + 1
                                h = Chr(hResults)
                                Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
                                hResults = hResults + 2
                                h = Chr(hResults)
                        End If
                    End If
                    endOfInventoryList = endOfInventoryList + 1
                Loop
        Else
            If Sheets(wkshtSearch).Range("B" & rowNum).Value = "" Then
                Sheets(wkshtSearch).Range("E" & rowNum).Value = "Enter an Amount"
            End If
        End If
        rowNum = rowNum + 1
    Loop
End Sub

Function temp(a, b)
    'THIS CODE DOES A PARTIAL VLOOKUP SO THAT IF THERE IS ONLY 1 DIFFERENCE BETWEEN THE TWO VALUES, THEN IT WILL COUNT AS A MATCH.
    cnt1 = 1
    errorCount = 0
    'Do Until: Find the first character of a within b.
    Do Until Mid(a, 1, 1) = Mid(b, cnt1, 1) Or cnt1 > Len(b)
        cnt1 = cnt1 + 1
    Loop
    If cnt1 > Len(b) Then
        errorCount = errorCount + 1
    End If
    cnt2 = 2
    Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
        cnt2 = cnt2 + 1
    Loop
    If cnt2 < Len(a) Then
        errorCount = errorCount + 1
    End If
    If errorCount < 2 And cnt2 < Len(a) Then
        cnt2 = cnt2 + 1
        Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
            cnt1 = cnt1 + 1
            cnt2 = cnt2 + 1
        Loop
    End If
    If cnt2 - 1 < Len(a) Then
        errorCount = errorCount + 1
        cnt2 = cnt2 + 1
        Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
            cnt1 = cnt1 + 1
            cnt2 = cnt2 + 1
        Loop
    End If
    If cnt2 < Len(a) Then
        errorCount = errorCount + 1
    End If
    If errorCount < 2 Then
        temp = True
    Else
        temp = False
    End If
End Function



I am playing with your code for work I am doing. I love the fact that it updates the whole worksheet!

I am using it to search various vin#s against my inventory to locate buyers who have purchased similar vehicles from me in the past. When searching the sales history, I only use the 1st 8 digits of the VIN#.

Ideally, when I get a hit, I would like the results to populate the the 1st 10 cells in that row? Example - if line 2 on the search worksheet lists vin# 2G1WH52K and I find 3 matches on the inventory worksheet on line# 132, 165 and 203, the code will copy the data in A132:J132, A165:J165 and A203:J203 and paste to the search worksheet in line 2 starting at B2:K2, M2:V2 and X2:AG2, etc...

From that I can quickly add up how many similar units (VIN# starting with 2G1WH52K) that each buyer purchased and set a MAX formule for the more current sale date.


Is this possible?

Thanks for all your help!
Dave
 
Upvote 0
Sounds like I solved your original issue and you are moving on to another topic. Please create a new post asking about your new topic. As wonderous and amazing as I am, I'm not the wizard of OZ. I don't know everything. When you post your new thread, make sure to add more detail and a dataset so everyone knows what you are talking about.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top