Complex Combinatorial sort VBA/Macro

Hi everyone,

I have a tricky task Let me clarify that step by step below.

Input
• table with 5 columns
• columns are: combination, place1, place2, place3, income
• column combination represents a number of each combination (combination of 3 places)
• place1, 2, 3 are basically points where a car needs to go
• income says how much we get paid to drive in a specific combination (way)

e.g. we can say that combination #6 brings 13628.40 income and the car needs to go through places A, O & M.

First of all, I need to find out the way how I specify: Let's say that formula will go from combination 1 to 197. We currently know that all combinations are sort from the biggest price to the lowest one.

Secondly, I need to set up the rule saying, once you drove through a specific place, DO NOT drive through that point again.
e.g. We take the combination #1 F, A, O and we know that cannot drive again to those points. So a formula looks for other combinations the next possible combination is #13 since M, K, N do not match with previous combination F, A, O. Now we have already 6 places where the car cannot drive anymore. F, A, O, M, K, N. Then the next available combination is E, I, J. And it goes like that until it does not have all possible combinations found.

We can say that all found combinations are below:

combination	place1	place2	place3	income
1	F	A	O	16566
13	M	K	N	12126
19	E	I	J	10610
74	Q	T	R	5500
93	G	H	C	4695

TOTAL Income is 49549. This result is based on the fact, that formula goes from combination #1 to #197 where biggest incomes are first. What if the table is sort in a different way? How many combinations can be there? 197 factorial? How can find the best distribution in order to get the highest income value?

PERFECT! It solves the first part of the problem, thank you so much.

The other question is still: Is there another way how to find the biggest total income? Or is total income 49549 really the biggest possible one?
I would love to have a macro which will be able to sort all combinations (from 1 to 197) in 197! (factorial) ways. It will record all sums of total income. Simultaneously, it should also assign how the combinations were sort for each total income. It means that at the end of the day I will be able to say: Ok, no I know that total income 49549 is the biggest possible total income where following combinations numbers exist: 1, 13, 19, 74, 93.

Thank you, I appreciate any idea.
PS: I guess it would take Excel forever to calculate such a formula but I hope there is some solution

I agree with you I just do not know how to assess this problem I do not say that is the way how to do that.

Thank you so much! Both approaches work perfectly how I want. Karedog's code is better for the overall overview and takes let than a minute. Much appreciated guys, you helped me a lot

Originally Posted by AliGW

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

No further help to be offered, please, until the OP has complied with this request.

Thank you for sharing that with me. I am sorry, I did not realize it. Here is the link: https://www.mrexcel.com/forum/excel-...vba-macro.html

Hello karedog,

Thank you again for your valuable help, I do appreciate it!

I have another challenging question related to this topic.
Please have a look on the attached file where I try to demonstrate what should be the ideal outcome
https://drive.google.com/file/d/1KA4...ew?usp=sharing

2018-03-17_23-53-46.png

The thing is that I would like to extend the outcome. I have 346 more combinations where are only 2 places to visit for each combination.
I would love to find all possible combinations between generated 184556 combinations and new 346 combinations based on the same restrictions as for the previous calculation. It means if there is no duplication in places both combinations can be merged to one.
Thank you so much for your help.

Hello karedog,

I see! Anyway, it helps me a lot, I will try to set up some rules to figure it out. Thank you.

I have been trying to solve a similar problem. There is a worksheet with combinations where there are only two places to visit. It's basically the same problem as the initial one but for now, I have only 2 places to visit per combination. Could you please help to find out how to generate all combinations based on the same rules as previously? It means basically that one place cannot be visited twice? Please find the attached Workbook.

Awesome, thank you so much! Eventually and hopefully, the final question regarding combinations? Is it possible to have a similar code for an example below? First generated combinations are in columns B,C,D and other secondly generated combinations are in columns H,I,J. Theirs merged combination are sum up in columns M,N,O. I am looking for the results in columns M,N,O. I put together two examples how it should look like. Unluckily, I was trying so many hours to create a proper VBA code without a useful solution Could you please advise?
file here: https://goo.gl/tVKVSj

combinations.png

That works perfectly! I have been working on reducing extra calculation in the code by setting up the rule where code checks whether is total sum of income higher than certain value (e.g. 45000). If yes, then it skips all the formulas and saves total calculation time otherwise if there is more rows in the sheet it takes a long time to get results. I cannot find the proper way how to set it up. Do you think there could be a way how to check first how much is total sum of two selected combinations is over the selected limit and if not, then continue to compare another pair of combinations (rows). Thank you a lot for your all help!

Hello,

Thank you for another solution. Doesn't it only generate first 25000 results? I was thinking to consider all possible results but only those results with total income higher than 45000 will be printed.

Thank you for your help.

Alright, I understand. I still want to keep calculating all combinations even thought the time will not change. Anyway, I need to display only those with total income higher than 25000. Since there is more results than the maximum amount of rows in Excel. Thank you.