Exclude data conflicts and then match remaining Data

re Your message,
Confusing

Originally Posted by Lee_of_Excel

My goal is to Match a list of 1-10000 USERID's with an appropriate SITEID.

I have a full table of SITEID's.

The USERID data will come in as a row in Excel, which shows:
1. USERID (Which is Numeric)
2. States of Australia to Exclude (could also be none)
3. 0 - 50 SITEID's to Exclude (alpha/numeric and numeric)
4. 0 - 5 SITEID Preferences (alpha/numeric and numeric)

My Matching algorithm includes the following rules:
1. Exclude USERID States from corresponding list of SITEID States (For example if a USERID inputs NSW and VIC, they will be excluded from all SITEID's in these states, could also have none)
2. Exclude USERID Conflict SITEID's A (e.g. If a USERID inputs 0-50 SITEID's, they will be excluded from their choice of SITEID's)
3. Match a USERID to SITEID Preference Match - they may input 0-5 preferences, (e.g. If the USERID's preference 1 matches a SITEID from the remaining list of SITEID's let over after Exclusions then Show the match in a new Cell, (Preferences have order of priority)
4. If there are no SITEID's to match the preferences show "No Match"

Excliude from what?

Originally Posted by Lee_of_Excel

So a full Example might be:
USERID 111 identifies NSW and VIC to be excluded
USERID 111 identifies SITEID 111, SITEID 112 and SITEID 113 to be excluded
USERID 111 preferences are SITEID's 115, 116 and 117
As SITEID 115 is in NSW this SITEID isn't available, so the algorithm tries preference 2 - SITEID 116
SITEID 116 is in QLD so we have a match of USERID 111 and SITEID 116
Then the Algorithm moves to the next USERID
(If the algorithm couldn't match any SITEID's from the USERID preferences then it would show "NO MATCH")
(Some SITEID don't have a State assigned or aren't in Australia so these can also show "NO MATCH")

Multiple USERID's can match the same SITEID's
Each USERID starts with a full list of SITEID's before exclusions occur

So the end product will show in the last column for each row (Each USERID), something like:
SITEID 111
SITEID 5435
No Match
SITEID 3267
No Match
No Match
SITEID 111

and so on.

I have attached the spreadsheet and I believe the solution is to Reference the STATEEXCLUSIONS against the StateTable (Table 2) as a dictionary, and Reference the SITEEXCLUSIONS against the SITETABLE (Table 1) as a dictionary to Exclude them from the full Site list and then Match the SITEPREFERENCES. I just can't write the syntax correctly.

Where can I find "USERID 111"?

OK, take 1st row i.e. USER ID = 11111

STATE/TERRITORY EXCLUSIONS = VIC
SITE EXCLUSIONS = 1143(corresponding value in SITE_TABLE = VIC) at SITE_ID_4
SITE PREFERENCES = 717(corresponding value in SITE_TABLE = Blank) at SITE PREFERENCES

What is the output and why?

Ok cool thanks,
for USER ID = 11111
STATE/TERRITORY EXCLUSIONS = VIC
SITE EXCLUSIONS = 1143(corresponding value in SITE_TABLE = VIC) at SITE_ID_4
SITE PREFERENCES = 717(corresponding value in SITE_TABLE = Blank) at SITE PREFERENCES

So their site preference of 717 doesn't have a State assigned - so in this case It would be "NO Match" and I would need to manually contact this person to discuss left over available sites after all of the Matches had been processed.

My main goal is to match as many People as possible, but where the data is not yet perfect, we will need to manually contact the person and manually match.

(The data is currently being updated and cleansed so I can run the algorithm multiple times as the data becomes better)

If that's the case, it only needs to search what is in SITE PREFERENCES against SITE_TABLE and return the result(s)(corresponding value) and when it is blank, mark as "No match".

Is this correct? or am I still missing something?

Originally Posted by jindon

If that's the case, it only needs to search what is in SITE PREFERENCES against SITE_TABLE and return the result(s)(corresponding value) and when it is blank, mark as "No match".

Is this correct? or am I still missing something?

Yes that is correct, it only needs to SITE PREFERENCES against the SITE _TABLE (excluding the SITE EXCLUSIONS AND STATE EXCLUSIONS)

If the preferences are Blank, then mark as "No Match"
If the State is missing beside a SITE_ID in the SITE_TABLE that was their preference it will also be "No Match" (Unless one of their other preferences can be matched)

For Example if:
USER ID = 31267
STATE/TERRITORY EXCLUSIONS = NSW and WA
SITE EXCLUSIONS = 1640 and 1670 (as well as all in NSW and WA)BLANK
SITE PREFERENCES = Preference 1 = 1640, Preference 2 = 1670, Preference 3 = 81, Preference 4 = 422, Preference 5 = BLANK

1640 is in WA so they can't work there
1670 is in NSW so they Can't work there
81 is in NSW so they can't work there
422 WAS in Blank for state, then it would look at preference 5 (if they had one)
In this example, if they had preference 5 = 1324 (Tas) they would have a match on preference 5

So in Column 'BM' it would say 1324

For another Example:

USER ID = 31267
STATE/TERRITORY EXCLUSIONS = NSW and WA
SITE EXCLUSIONS = 1640 and 1670 (as well as all in NSW and WA)BLANK
SITE PREFERENCES = Preference 1 = 1640, Preference 2 = 1670, Preference 3 = 81, Preference 4 = 422, Preference 5 = BLANK

1640 is in WA so they can't work there
1670 is in NSW so they Can't work there
81 is in NSW so they can't work there
422 is in NT so this would be a Match

So in Column 'BM' it would say 422

Then the algorithm would move to the next row

In the case above, how would be the output for that row?

And one more,
Say if
STATE/TERRITORY EXCLUSIONS = NSW
SITE EXCLUSIONS = None
SITE PREFERENCES =1143(VIC)
When something like this happens, it still need to exclude 1143?.
I mean exclude all the states in STATE_TABLE?

OK, this is how I understand...

1) Check the columns B:BG and get all states that you want to exclude.
2) Check the columns BH:BL and see if their corresponding State are listed in 1)
3) If corresponding state is not in the list, output the number in that cell, when corresponding state is blank, "No Match".

If above is true, what if there are multiple valid results?
Say one or even more has the corresponding state(s) that is not in the list and one/more has blank(s)?

Yes thats all correct,

For multiple valid results, is it possible just to show only the first valid result?

If not, could we have these output all valid results in the next columns (e.g. BM, BN, BO, BP and BQ?

Both are possible and even multiple results in one cell.
All I need to know is how you want it.

Excellent, in that case, can I please have the output of all valid results in the next columns (e.g. BM, BN, BO, BP and BQ?

Awesome! Thanks very much! i'll get some live data in the morning and test it out. Cheers

Hi Jindon,

Thanks again for your work on this,

I tested the logic on our example USER_ID's and got the following results:

USER_ID = 11111, Result = 717, but should be "No Match"
USER_ID = 15452, Result = 1510, but should be "1676"
USER_ID = 31267, Result = 422, which is correct
USER_ID = 15452, Result = Blank, which should be "no match'

The reasons:
USER_ID = 11111, Result = 717, but should be "No Match" - because 717 has no corresponding state in the Site_Table, so we don't know if it is in a state that the user can't work in and will have to be manually completed
USER_ID = 15452, Result = 1510, but should be "1676" - because this user reported that they couldn't work in SA or Site 83 and their first preference was 1676 which is in QLD, so 1676 will be a correct match
USER_ID = 31267, Result = 422, which is correct - the user identified that they couldn't work in NSW and WA and site 1640, and site 1670. 1640 and 1670 were their 1st and 2nd preference so they couldn't have them and their 3rd preference was in NSW so they couldn't have that but their 4th preference was 422 in NT so that is a correct match
USER_ID = 15452, Result = Blank, which should be "no match' - the user identified that they couldn't work in VIC or site 81 but had no preferences so the correct result is "no match" (which I think was the output your logic output)

That was the bug I have fixed after I posted, so copy the code again and try.

Got the result from your attached workbook.

717 (No Match)
409 (No Match)
422

Looks good, I think that the logic is working well now, this will work well thanks,

Now that the logic is working well, can it output all 5 preference results in separate columns, I have updated the spreadsheet ready :-D

Is this the final format and the headers will not change?
I mean header name for PREFERENCE_OUTPUT_#.

Yes this will be the final format, I be the only person viewing this spreadsheet so I didn't make it too pretty. cheers

As before, the code is looking at the merged areas in 1st row.
As long as you keep the rule of merge, it should be flexible to increase/decrease columns.

Sub test()
    Dim a, b, x, i As Long, ii As Long, rngSite As Range
    Dim myCols(), n As Long, myS, temp, t As Long
    Set rngSite = Sheets("SITE_TABLE").Cells(1).CurrentRegion
    With Sheets("matching").Cells(1).CurrentRegion
        t = Application.Match("preference_output*", .Rows(1), 0)
        For ii = 1 To t - 1
            If .Cells(1, ii).Address = .Cells(1, ii).MergeArea(1).Address Then
                n = n + 1: ReDim Preserve myCols(1 To 2, 1 To n)
                myCols(1, n) = ii: myCols(2, n) = .Cells(1, ii).MergeArea.Count + ii - 1
            End If
        Next
        If n <> 4 Then MsgBox "Number of merged area in 1st row should be 4", vbCritical: Exit Sub
        a = .Resize(, t - 1).Value
        .Columns(t).Offset(2).Resize(, myCols(2, 4) - myCols(1, 4) + 1).ClearContents
        b = .Columns(t).Resize(, myCols(2, 4) - myCols(1, 4) + 1).Value
        For i = 3 To UBound(a, 1)
            ReDim myS(1 To myCols(2, 2) - myCols(2, 1) + 1)
            For ii = myCols(1, 2) To myCols(2, 2)
                If a(i, ii) <> "" Then myS(ii) = a(i, ii)
            Next
            For ii = myCols(1, 3) To myCols(2, 3)
                If a(i, ii) <> "" Then
                    x = Application.VLookup(a(i, ii), rngSite, 2, False)
                    If Not IsError(x) Then
                        If x <> "" Then
                            ReDim Preserve myS(1 To UBound(myS) + 1)
                            myS(UBound(myS)) = x
                        End If
                    End If
                End If
            Next
            For ii = myCols(1, 4) To myCols(2, 4)
                If a(i, ii) <> "" Then
                    x = Application.VLookup(a(i, ii), rngSite, 2, False)
                    If Not IsError(x) Then
                        If x <> "" Then
                            If IsError(Application.Match(x, myS, 0)) Then
                                b(i, ii - myCols(1, 4) + 1) = a(i, ii)
                            End If
                        Else
                            b(i, ii - myCols(1, 4) + 1) = a(i, ii) & " (No Match)"
                        End If
                    Else
                        b(i, ii - myCols(1, 4) + 1) = a(i, ii) & " Invalid Entry!"
                    End If
                End If
            Next
        Next
        .Columns(t).Resize(, UBound(b, 2)).Value = b
    End With
End Sub

Great thanks, I think this is all working well now,

If I add a vba line at the end to fill all blank spaces with "No Match" in the blank reference output fields, would that cause an issue?

I was just thinking that in the future if someone has to fill in or assist me with using this spreadsheet, they might become confused.

You mean?

            For ii = myCols(1, 4) To myCols(2, 4)
                b(i, ii - myCols(1, 4) + 1) = "No Match"  '<--- add this line
                If a(i, ii) <> "" Then

Yes I think that might work, i'll give it a try

It is looking great Jindon, I have given it to some Colleagues to also test in case I missed anything