VBA Complex Matching Procedure Approach Problem

rippchen10247

New Member
Hey all,

though I can code in Visual Basic, this is my first time in smaller VBA. For my job, I need to apply a more complex matching procedure in excel.

Specifically, I try to match two companies in the following manner: I have 2 company lists with about 1000 companies on list 1 and 5000 companies on list 2 respectively.

The lists have identical colums for each company entry, e.g. the market value of a company, pricing dates etc. My aim is to match the companies one on one, so that each company of the first list is matched with one company of the second list. The matching should refer to criterias based on the other colums / data entries for each company.

For example: Company A (list1) is matched with Company B (list2) based on information about the market value of a company and pricing dates, whereby the companies should match in both criteria as accurately as possible. E.g. List 1 is for reference and company A should be matched with a company B out of list 2, when B has a market value as well as pricing dates in certain definable ranges around the values of Company A.

Moreover, I want to apply let say 10 criteria for matching and if the conditions are not fulfilled cumulatively, at least the first 9 criteria should be fulfilled for a match, if not possible at least the first 8, if not possible at least the first 7 and so on. After all, every company on list 1 should should be matched with a certain company of the bigger list 2.

Do you have any (parts of) solutions how to code this?

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Mowgy

New Member
Hi there,

I only just started using VBA not too long ago so there may be better ways of proceeding on this.
A loop of IF xxxxx = true (or using instr) and yyyyy = true and so on. If it matches nothing make the cell a value that triggers the next one down with a If x = "nothing" then one that goes through your next 9 criteria?

As far as I understand your criteria it should match are either all 10 or then just the first 9 then first 8.
Or is it that you want it to match any 9 in any order, so search parameters 1 through 8 match, 9 doesn't and 10 does triggers the match ?

Just my two cents...

rippchen10247

New Member
Hi there,

As far as I understand your criteria it should match are either all 10 or then just the first 9 then first 8.

You got me right there. Thanks for your fast reply. How would you imply the first two criteria (e.g. market value of company & price) for the matching in code exactly?

@ All: Any further suggestions?

Mowgy

New Member
If it is mean to be an exact match then something along these lines :

(NB : I am new to VBA so there may be a much simpler way to do this)

Code:
``````For x = 1 To 1000    For y = 1 To 2000
'This being what it is meant to match
LookupValue1 = ThisWorkbook.Sheets("sheetname").Cells(x, 1).Value
Lookupvalue2 = ("and so on down to how ever many lookups you have")
If ThisWorkbook.Sheets("sheetname").Cells(y, 2).Value = Lookupvalue1 And (next cell and lookupvalue2) And ..... Then
ThisWorkbook.Sheets("sheetname").cell(x, columnwhereyouwantyourinfo) = ThisWorkbook.Sheets("sheetname").cell(y, "Cell column where company name is").Value
End If
Next y
Next x``````

Mowgy

New Member
If it is mean to be an exact match then something along these lines :

(NB : I am new to VBA so there may be a much simpler way to do this)

Code:
``````For x = 1 To 1000    For y = 1 To 2000
'This being what it is meant to match
LookupValue1 = ThisWorkbook.Sheets("sheetname").Cells(x, 1).Value
Lookupvalue2 = ("and so on down to how ever many lookups you have")
If ThisWorkbook.Sheets("sheetname").Cells(y, 2).Value = Lookupvalue1 And (next cell and lookupvalue2) And ..... Then
ThisWorkbook.Sheets("sheetname").cell(x, columnwhereyouwantyourinfo) = ThisWorkbook.Sheets("sheetname").cell(y, "Cell column where company name is").Value
End If
Next y
Next x``````

rippchen10247

New Member
If it is meant to be an exact match then something along these lines :

No I do not mean an exact match. Let me depict my problem for two matching criteria instead of 10.

List 1 (1000 companies total)

Name Market Value (Mio) Pricing Date
Company A 50 1.8.2012
Company B 100 1.1.2013
.......

List 2 (5000 companies in total)
Name Market Value (Mio) Pricing Date
Company 1 500 1.9.2012
Company 2 300 2.1.2013
Company 3 150 4.5.2012
Company 4 90 3.2.2013
Company 5 30 3.8.2012
Company 6 45 1.10.2012
.......

Now, I want to Match all Companies of List 1 with exactly one company of list 2. Double Matches are allowed. It should start with finding a reference company for Company A out of list 2. The reference company should be a one which has let's say #criteria1# a market value within the range of 50 (+/- 20% deviation) and which was #criteria2# priced within the same time as company A was (+/- 3 months). Both criteria should fulfill cumulatively, if not possible, at least the first criteria should be fulfilled.

After all, I want to have every single company of list 1 matched with exactly one company of list 2. The rest of the companies in list 2 may be dropped out, since I only need pairs of companies which fit as good as possible. Double Matching (e.g. Company A and B are both matched with the same company of list 2) is allowed.

Last edited:

Mowgy

New Member
I see... however seems a little far out from what I know how to do, maybe someone else has solutions that can work.

Sorry and good luck

Anyone?

Replies
2
Views
133
Replies
3
Views
525
Replies
19
Views
740
Replies
2
Views
254
Replies
7
Views
142

1,196,021
Messages
6,012,904
Members
441,740
Latest member
Latrs

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.

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

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