Macro to lookup partial matches in 2 columns

mExd2014

New Member
Joined
Dec 30, 2014
Messages
27
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

dssJones

Board Regular
Joined
Nov 3, 2016
Messages
71
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

New Member
Joined
Dec 30, 2014
Messages
27
thanks - this could be useful

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

dssJones

Board Regular
Joined
Nov 3, 2016
Messages
71
MID is a VBA string function.
You can use the LEFT, Excel function if not VBA.
 

dssJones

Board Regular
Joined
Nov 3, 2016
Messages
71
Are potentially matching values on different rows?
 

dssJones

Board Regular
Joined
Nov 3, 2016
Messages
71
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
VBA Code:
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

New Member
Joined
Dec 30, 2014
Messages
27
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

Board Regular
Joined
Nov 3, 2016
Messages
71
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

Board Regular
Joined
Nov 3, 2016
Messages
71
Try the InStr() method.
It produces a substring like MID does except it asks the question "is this string in that string", hence smarter.
 

Forum statistics

Threads
1,089,636
Messages
5,409,456
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top