VBA - How to Ignore array dimension mismatches? - move to next

I have 2 arrays, my VBA macro uses these arrays to match one against the other, but there can be a different amount of entries in 1 array than the other.

When there is a different amount of entries, the macro stops on Err.Raise -1001, , "Array dimensions do not match", but I want it to skip and continue matching the available entries.

How do I skip the blanks and match entries that are available?

Please Login or Register  to view this content.

Originally Posted by Lee_of_Excel

I have 2 arrays, my VBA macro uses these arrays to match one against the other, but there can be a different amount of entries in 1 array than the other.

What do you want when you find match?

You can loop through all the dimensions or use Match function to find, if needed, so why do you need to ignore the dimension?

I don't fully understand the logic to "match".
This outputs if 1st preference in both men/women matches, also no preference.

Please Login or Register  to view this content.

Thanks Jindon, your code works well for the first match.

I don't have a great understanding of how the match works either, unfortunately I inherited the matching code and am stuck with trying to modify it.

I have another style of the same code which also works but it places the results in a msgbox.

This also has the same issue though, I need to be able to enter different amounts of entries and also want to use the Sheet1, Sheet2, Sheet3 style like you have created.

Thanks again for your help

Cheers

Please Login or Register  to view this content.

Sorry, but if you don't understand the logic to match, I can not modify my codes.

If you want to output to the sheet instead of msgbox
replace

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Originally Posted by jindon

Sorry, but if you don't understand the logic to match, I can not modify my codes.

If you want to output to the sheet instead of msgbox
replace

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hi Jindon, do you happen to understand Python?

I have the python algorithm below, but cannot translate it in to Excel VBA.

Please Login or Register  to view this content.

Hi Jindon and All,

It has been a while, but can I please ask one more favour.

With your code, how can I use the split function to pull data from 2 tables, which may be different in size?

Currently I have Trainee names in Column B, from B4:B7, followed by their preferences of sites in row (C4:H7),

Then I have Sites from B12:B22, followed by their preference of trainees in row (C12:H22)

The amount of trainees and amount of sites may vary and the number of their preferences may vary.

I want to try and use your VBA code to recognise data entered in the 2 tables.

I have enclosed the spreadsheet and screenshot as an example.

Thanks again

Lee

The 'Non Working' code is:

Please Login or Register  to view this content.

Originally Posted by jindon

This outputs if 1st preference in both men/women matches, also no preference.

Also even if I could run the same module multiple times that would be fine for example. If I run the module the first time, it matches 1st preference in both Trainee/site matches, then I could run a second module which is the same, but looks at second preference for rows that weren't able to match using the 1st preference.

Sorry, but I don't even read the code built by someone else and touch it.
If you upload a workbook with the data and the EXACT results that you want clearly showing what you are trying to achieve, it may help.

Originally Posted by jindon

Sorry, but I don't even read the code built by someone else and touch it.
If you upload a workbook with the data and the EXACT results that you want clearly showing what you are trying to achieve, it may help.

Thanks again for your help Jindon, I have now removed the code and provided a description for the results that I am trying to get in the Workbook.

Goal:

1. To compare Table1 (Blue) and Table2 (Orange) - both may change in the amount of entries. e.g 70 trainees vs 140 sites etc

2. Match the preferences between Table1 and Table2 (even if it can only match the first preference of each) e.g Trainee 01 first preference is site 04 and Site 04 first preference is trainee 01 - so they have a match

3. Fill the match results in Table3 (green)

4. If the results have any with no match, I would like to run a copy of the code a second time, which will compare second preference and fill in the data in Table3 and so on until the most possible have a match from their preference list

This is how I understand...

Please Login or Register  to view this content.

Note:
I need to go out shortly, so if any further adjustment or something else, my response will be late.

Originally Posted by jindon

This is how I understand...

Please Login or Register  to view this content.

Note:
I need to go out shortly, so if any further adjustment or something else, my response will be late.

Thanks Jindon, This seems to be working really well, the only thing is when I add in extra rows like Trainee 06, 07, 08 etc it doesn't seem to be adding the new entries to the match results, is there a way to fix this so when table 1 and/or table 2 is expanded, the results (Table3) will add in the new entries?

1) Don't full quote the post unless you really need to.
The forum doesn' like it.

2) It should run all the data in both tables.
If it diesn't, need to see your workbook
As I'm already out, I can only see it after I get back.

Change to

Please Login or Register  to view this content.

Perfect! Thanks very much Jindon :-D

Originally Posted by Leith Ross

Hello Lee_Of_Excel,

Yes, I do. This is an example workbook. It has several tables with the UDF results along with the hand calculated results to verify the UDF is function correctly.

This is great!! thanks very much Leith, I hadn't even considered a function, although I don't yet have the skills to write anything like this. Thanks again

Cheers

Can you just try change 2 lines in bold and see if it works,
1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

Originally Posted by jindon

Can you just try change 2 lines in bold and see if it works,

Thanks Jindon, It didn't seem to work,
123879 still got the results:
123879:303E7
123879:863E7
123879:5C3E7

I see what you mean "Any order",
Which Preference number should be in the result when P/N doesn't match?

For my use, Trainee would have priority, so if Trainee Preference:

Trainee: 123879 had
Preference 01: 443E7
Preference 02: 303E7
Preference 03: 863E7

And Site: 443E7 has Trainee 123879 in any preference, this can match as first priority.

followed by any others

Hummm, this will pick up almost everything...

Please Login or Register  to view this content.

Nice! thanks! this is looking good, I'll do some testing :-D, am I able to place the 'Site Code' in the results like before instead of the preference number?

e.g.
123879:Preference 6
123879:Preference 1
123879:Preference 2

to
123879:AF3E7
123879:443E7
123879:303E7

Just try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

This will to show you the 3rd column of result "Preference(s)".
I need to go out soon, so any changes will be late...

Thanks, that worked!

I think I am still missing 1 last piece of code which will identify the final correct result.

The correct result for Trainee:123879 should be site: 443E7 as the Trainee picked this as Preference 01 and The site picked the Trainee as Preference 02.

The initial code gave the result: 123879:303E7 as they both had each other as Preference 02.

The new code gives the result: 123879:AF3E7 as the first occurrence as The trainee preference for this site was Preference 07 and the Site has the trainee as Preference 01, But it also has all of the combinations, which I was looking for, but I am not sure how to rank/weight these in code to ensure that the Trainee gets their most preferable match, in this example 123879:443E7

Any ideas?

Originally Posted by jindon

This will to show you the 3rd column of result "Preference(s)".
I need to go out soon, so any changes will be late...

Thanks very much, I'll test it out.

Cheers

Looks like its working quite well!

I added a sheet 'Rank Matrix' and I am using your 3rd column results to index a rank matrix. I use 'text to columns' to split the 3rd column results into 2 columns on the rank matrix sheet (Trainee Preference # and Site Preference #) and then index against the matrix. It's very inelegant, but my vba skills aren't yet good enough to tidy it up.

Then I added a 4th column on the match results beside your preference column to sort the results by matrix rank result.

This solution seems to be working for all the tests I have done so far.

Is there a way to have the text to columns, matrix index and sorting all done in vba also?

Thanks again for your help, this will be a really useful tool.

So the code is listing correct result?

Please Login or Register  to view this content.

Originally Posted by jindon

So the code is listing correct result?

This is Fantastic!! thanks very much for your help Jindon. Yes it seems to be listing the correct results, I have a few lists from 2019 to go through to see if the results match, but so far everything looks really good.

Cheers