Hey Guys,
I'm new to this forum. My name is Hugues. I need your help for a VBA code. My excel sheet contains only 2 columns called: List (cell A1) and results (cell C1).
In the list columns(A2, A3, A4 and so on), i have a list of words. What i want the VBA to accomplish is to combine(permute without repetition of the same words in the sentence) all the words from the list up to a maximum of a combinations of 3 words. Do you think it would be possible?
Many thanks in advance for your help !
Last edited by McGyver270; 01-25-2021 at 06:17 PM. Reason: Sheet added
read the yellow banner at the top of this page
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Sorry Alan.
I've added the sheet to my original post.
Hi McGyver270,
Welcome to the forum!!
Change any of the four variables I've commented with "Change to suit if necessary" and then try this while on the sheet that has the data in the 'strMyCol':
To check the number of combinations use the COMBIN function i.e. =COMBIN(6,3) in your example.
Please Login or Register to view this content.
Keep in mind that permutations can grow quickly - so much so that they can exceed the number of rows on an Excel sheet!!
Note the credit in the code to Weaver for the User Defined Function (UDF) that does the grunt work.
Regards,
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Hi Robert,
Thank you very much for your code, it's greatly appreciated.
I'm not sure if i understand your comment Change any of the four variables I've commented with "Change to suit if necessary" and then try this while on the sheet that has the data in the 'strMyCol':
Can you expand more please?
Thank a lot.
Hugues
Edit: While running the macro, i get the following error: ''compile error user-defined type not defined''. Any idea what is causing this? Thank again for your time.
Last edited by McGyver270; 01-26-2021 at 01:01 AM. Reason: Comment added
You could do this with formulas.
E1:
Formula:
Please Login or Register to view this content.
E2:
Formula:Please Login or Register to view this content.
F2:
Formula:Please Login or Register to view this content.
G2:
Formula:Please Login or Register to view this content.
H2:
Formula:Please Login or Register to view this content.
Select E2:H2 and fill down until formulas return #N/A errors.
You can change the values of these four variables in the ListAllCombinations macro to meet your needs, if required:I'm not sure if i understand your comment Change any of the four variables I've commented with "Change to suit if necessary":
Please Login or Register to view this content.So you need to run the ListAllCombinations macro while you're on the Feuil1 tab and the data needs to be in the column that's been assigned to the "strMyCol" variable (A in my original code).try this while on the sheet that has the data in the 'strMyCol':
Not sure why but there's a recordset variable that is in the function. I have removed it from the following and it then ran without issue (you could make a reference to Microsoft ActiveX Data Objects in the Visual Basic Editor to also solve the issue but it seems to serve no purpose (that I can see anyway)):Edit: While running the macro, i get the following error: ''compile error user-defined type not defined''. Any idea what is causing this?
Please Login or Register to view this content.
Last edited by Trebor76; 01-26-2021 at 02:26 AM.
Robert,
Many thanks for taking the time to make the correction, it work fine except that its not the results i was looking for. It doesn't give me all the combinations possible. I will give you another example of possible outcome i'm looking for:
Lets says we have the words: Cat, Dog, Monkey and Bird
The functions i need should return the combinations:
Cat
Dog
Monkey
Bird
Cat-dog
Cat-Monkey
Cat-bird
Dog-monkey
Dog-bird
Dog-cat
Monkey-bird
Monkey-cat
Monkey-dog
Bird-cat
Bird-dog
Bird-monkey
Cat-dog-monkey
Cat-dog-bird
Cat-monkey-bird
Cat-monkey-dog
Cat-bird-dog
Cat-bird-monkey
Dog-cat-bird
Dog-cat-monkey
Dog-bird-monkey
Dog-bird-cat
Dog-monkey-bird
Dog-monkey-cat
Bird-dog-cat
Bird-dog-monkey
Bird-cat-dog
Bird-cat-monkey
Bird-monkey-dog
Bird-monkey-cat
Monkey-dog-cat
Monkey-dog-bird
Monkey-cat-dog
Monkey-cat-bird
Monkey-bird-cat
Monkey-bird-dog
The only condition is that the formula should not give a combination with the same word, ie: Cat-cat or in the case of 3 words: cat-dog-cat
Sorry again, i should have explained it better the first time.
Hugues
There has to be a more efficient way but this works:
Please Login or Register to view this content.
Here's a more succinct version that can still be improved:
Please Login or Register to view this content.
Last try:
Please Login or Register to view this content.
Another Option...
Please Login or Register to view this content.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Robert,
Thank you very much for your work. Macro2 work better for my case. If i can make a contribution for your work, i will be more than happy to send you some $
A thank also to hrlngrv and sintek for your contribution to this thread.
Hugues
You're welcome. Thanks for letting us know.Thank you very much for your work. Macro2 work better for my case.
No, this section of the forum is free so I will not accept payment. If you could mark the thread as solved it would be appreciated.If i can make a contribution for your work, i will be more than happy to send you some $
Regards,
Robert
No problem. Thank much again, Robert !
Thank you (and for the rep)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks