VBA Complex Matching Procedure Approach Problem

[rippchen10247]

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?

 

[Mowgy]

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]

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]

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)

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]

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)

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]

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.

 

[Mowgy]

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

 

[rippchen10247]

Anyone?