Macro to lookup partial matches in 2 columns

[mExd2014]

Hi guys

I have columns as follows;

Column A - Company Name
Column B - Company Balance
Column C - Company Name
Column D - Company Balance

I want to identify exact/partial matches in columns A and C and find the lower of the two balances

E.g.

Joe Bloggs Limited £100 to be matched with Joe Bloggs Ltd £200 with the result giving me a value of £100

Any ideas how to tackle this would be great.

Thanks

 

[dssJones]

The "MID" string function may be of use. However, you will have to determine (and code) an acceptable criteria for matching. "MID" uses a specified length of a string variable to create a sub-string. The criteria would be the minimum number of characters to accept as a match. (Assuming: the substring is to be derived starting from the first character) Given your example
MID("Joe Bloggs Limited", 1, 10) = "Joe Bloggs" and
MID("Joe Bloggs Ltd", 1, 10) = "Joe Bloggs"

 

[mExd2014]

thanks - this could be useful

Any ideas how to embed into VB to then pull out the lines with matches

 

[dssJones]

MID is a VBA string function.
You can use the LEFT, Excel function if not VBA.

 

[dssJones]

Are potentially matching values on different rows?

 

[mExd2014]

Yes they could be anywhere within the column

 

[dssJones]

This worked in my environment. Test it in yours
I pull company and amount matches and put them in columns E and F
You can change the code to fit your needs
You can change the criteria from 10 characters to whatever is appropriate

Sub Pull_Matches()
Dim rowA As Long
rowA = 2

Dim rowC As Long
rowC = 2

Dim rowE As Long
rowE = 2

Dim crit As Integer
crit = 10 'Criteria


' Assumption: Row 1 are for labels, data starts in Row 2
'   Match report will be in Column E (Company) Column F (amount)
    Dim companyA As Range
Set companyA = Sheet1.Cells(rowA, 1)

Dim companyC As Range
Set companyC = Sheet1.Cells(rowC, 3)

Dim companyE As Range
Set companyE = Sheet1.Cells(rowE, 5)

Do Until companyA.Value = vbNullString
rowC = 2
Set companyC = Sheet1.Cells(rowC, 3)
Do Until companyC.Value = vbNullString
If Mid(companyA.Value, 1, crit) = Mid(companyC.Value, 1, crit) Then
Select Case companyA.Offset(0, 1) < companyC.Offset(0, 1)
Case True
companyE.Value = Mid(companyA.Value, 1, crit)
companyE.Offset(0, 1) = companyA.Offset(0, 1)
Case Else
companyE.Value = Mid(companyA.Value, 1, crit)
companyE.Offset(0, 1) = companyC.Offset(0, 1)
End Select
rowE = rowE + 1
Set companyE = Sheet1.Cells(rowE, 5)
Exit Do
End If
rowC = rowC + 1
Set companyC = Sheet1.Cells(rowC, 3)
Loop
rowA = rowA + 1
Set companyA = Sheet1.Cells(rowA, 1)
Loop
End Sub

 

[mExd2014]

Thank you for this, it is very interesting and does throw up some of the matches, potentially I could have a couple of buttons for the various character combinations to.

Just a tweak on the code, is there a way I can get it to pull the exact name (either from Column A or B) in column E alongside the balance?

Additionally sometimes there is a company like J Sainsburys to be matched with Sainsburys plc - the mid function doesn't pick this up?

 

[dssJones]

That's the problem. Sure Mid can pick it up but you have to change the criteria so that the search starts at the third character. The code will do the same thing every time; the code is not smart enough to change criteria per iteration. Yea: you can just say companyE.Value = companyA.value or
companyE.Value = companyC.value
Perhaps you can make it smarter.

 

[dssJones]

Try the InStr() method.
It produces a substring like MID does except it asks the question "is this string in that string", hence smarter.