help with a combination calculator

[thoandros]

Dear all,
the below code works perfectly to create combinations with the values from 6 columns, and it's very quick too. The only problem is that it only works if there are at least 2 values in each column, whilst I could have cases where a column has just one value. I tried to figure out how to do that but I could not, is anyone able to help me? thank you in advance!

Please Login or Register  to view this content.

[Olly]

You could use Power Query, instead. Format your source data as a table, then use:

Please Login or Register  to view this content.

[thoandros]

Thank you Olly however this won't work too well for me I am afraid, because the file is returning the values to combine exactly in this specific way and it would take me too long to change the output.

As a workaround I am using the below code which works fine, even if it is way slower than the other code and I need another bit of macro to have the results made "text to column".

Please Login or Register  to view this content.

Are you suggesting to use a different approach because my original request cannot be changed to consider also columns with just one value?

thank you

[Olly]

Just change this section:

Please Login or Register  to view this content.

[WideBoyDixon]

Assuming that there aren't any column headers:

Please Login or Register  to view this content.

WBD

[jindon]

The only problem is that it only works if there are at least 2 values in each column, whilst I could have cases where a column has just one value. I tried to figure out how to do that but I could not, is anyone able to help me?

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[thoandros]

Hello and sorry for the late response on this.
Both the correction suggested by jindon and the alternative code suggested by Wide Boy Dixon work, so thank you both! however I prefer to follow jindon's suggestion because indeed there is an header.

Now, I got stuck again with another bit.

Please consider that each column contains values of 3 2-digit codes separated by a comma, example:
W3,W5,W6

By combining the 6 columns using a CONCATENATE I end up having a series of 18 2-letter codes. example:
S5,W3,W5,S5,W3,W5,R9,S6,W5,S6,W2,W5,R1,R2,S3,S5,W1,R7
This is then "exploded" in 18 cells and a formula identifies if there is any duplicate value in the row.

Consider that I might end up having something like 14 million combinations, based on the number of values in each column. So big numbers and therefore huge time for calculation.

I put a code to calculate the combinations only when these are under 500k as this reduces the macro time to approx 2 min, but this is still long.

The point is that the macro itself is quick to generate the combination, but as I said - and this is where I need your help - I must check that there is no repetition in any of the 18 results.

Is it possible for the macro to skip to generate a line of combinations when any of the three 2-letter values within a cell is going to be present in any of the other cells of the same line?

I cannot have a macro to combine 18 columns because I still have to consider the groups by three 2-letter values.


I hope I made sense. Basically a code that works like this:

Let's take in consideration cell A1: this has A1,A2,Y6
Identify which are the cells in column B that with values that do not contain any of the above 3.
Then identify which cells of column C that within their values have any of the above 6
Then from column D consider only cells without same as the above 9
Then from column E consider only cells without same as the above 12
Then from column F consider only cells without same as the above 15

Once I have found all the cells of column B,C,D & E that would create with the values in cell A1 the combinations of 6x3 different values then go ahead and generate them.

Then go with A2 etc etc

Thanks in advance to anyone that can find a solution for me!

[thoandros]

it might be what I want to achieve and explained i the previous post (the generation of combinations after evaluation that there are not duplicate values) is quite hard to do via a sigle code or at least I cannot figure it out.
so what I tried now is to have at least half of the job done by creating the combination of the 6 columns, but "exploding" it into 6x3 columns.
I tried to modify the code into this:

Please Login or Register  to view this content.

but the results are deleted just as soon as they are put in a cell, leaving just the third columns with values in it.

where am I doing wrong?

could the code by WideBoyDixon be changed to achieve this result?

[jindon]

No need to make it 3.

Please Login or Register  to view this content.

You can not store a single cell as an array, so resize(,2) makes multiple cells and you can load it to an array.
i.e

Please Login or Register  to view this content.

No need to have header anyway.

[thoandros]

Hi Jindon,

not sure why I changed to (,3) - maybe cause I thought that if I change to A2 (because there is the header in row 1) I had to change also that by +1

thanks for explaining about the array, but I am afraid don't get the second part of your post. what should I change in my code to make that it leaves the values generated in all columns?

At the moment it leaves only the last of each 3 columns...

See the attached file so you understand what I mean, thanks



Once this is sorted, what should I add in the code so that it automatically enters a formula in column AK(let's just say for the moment a concatenate all previous 18 columns), would that be possible?


thank you for your great help

[jindon]

Do you want to concatenate s:aj ?
After your code, run this

Please Login or Register  to view this content.

[thoandros]

thank you Jindon, the concatenate works well! but I still have the issue of the two columns disappearing. have you tried the file to understand what I mean with disappearing?

[thoandros]

also Jindon, can I ask some of your wonderful assistance to tweak a bit the concatenate bit you just helped me with?

I tried and tried and it keeps me giving syntax error

basically, what I need is that the concatenate is subject to this IF formula:

=IF(SUMPRODUCT(COUNTIF(RC[-18]:RC[-1],RC[-18]:RC[-1])-1)>0,""0"",CONCATENATE(RC[-3],RC[-2],RC[-1]))

So the concatenate is actually just of the last three columns of 18 (16,17,18)

the formula sumproduct etc is needed to identify that within the 18 cells there is no repetition (and if so, return a 0).

Let me know, thank you so much

[jindon]

You are over-writing one after another.

Please Login or Register  to view this content.

[thoandros]

it works perfectly! thank you very much!

Now, just for the last bit, as I told you in the previous post, I need to tweak the concatenate formula with another one (IF / SUMPRODUCT / COUNTIF) and only if there are no repetitions among the 18 values, to return a concatenate of column 16,17 and 18..

I tried using the below but it gives me a syntax error I cannot correct

Please Login or Register  to view this content.

certainly it's something you see straight away where the problem is ... I tried changing the ) and " somehow but can't figure it out.

thank you very much

[jindon]

Do you mean concatenate only unique value?
add bold lines

Please Login or Register  to view this content.

[WideBoyDixon]

Adaptation of my earlier code:

Please Login or Register  to view this content.

WBD

[thoandros]

Do you mean concatenate only unique value?
add bold lines
Copy to clipboard

Please Login or Register  to view this content.

not really what I was trying to do.
basically IF there are no repetitions among the 18 values, the column at the end has to return concatenate of values in columns 16,17,18, otherwise a 0.

Look at the file I attach here, I manually entered the formula but I was wondering how to code it so that it adds it automatically.

[thoandros]

Adaptation of my earlier code:

Please Login or Register  to view this content.

WBD

thank you, this works fast as well.

as I explained to Jindon, best for me would be that at the end of each row it adds a formula that returns a concatenate of the last 3 columns values, but only if there is no duplicate value across the 18. In origin I thought that it could be considered prior to generate combination, but this might be too complicated or slow down the macro too much, so even using the formula
=IF(SUMPRODUCT(COUNTIF(S2:AJ2,S2:AJ2)-1)>0,0,CONCATENATE(AH2,AI2,AJ2)) could work, but I don't know how to put it in the code

[jindon]

Then

Please Login or Register  to view this content.

[thoandros]

oh, I just realised it does already the exclusion of the rows with duplicates! That is brilliant!

Adaptation of my earlier code:

Please Login or Register  to view this content.

WBD

sorry

[WideBoyDixon]

Yes; I already took care of the uniqueness requirement. If you want to concatenate the last three columns into column AK then this:

Please Login or Register  to view this content.

WBD

[thoandros]

thank you WBD this works great!
and also thanks to Jindon.


@WBD I wonder if there is a limit of combinations. I noticed that when the product of number of variables is > 1,000,000 it ends up with an error. Is there anyway we can push it a bit further? I am quite ok with this limit, it takes approx 25 secs to elaborate the rows for 997k, which is acceptable, but I wonder if I could go at least to 1,500,000. If not, not the end of the world.

[WideBoyDixon]

With such high numbers, you're always going to run into difficulties with either Excel's capabilities or your computer's resources (CPU, Memory). I can't think of a way to streamline this right now.

WBD

[thoandros]

no worries WBD, it's really ok as is thank you!

with this formula it has elaborated rows with a product of 999936 in 25 seconds. Before, with the old code I initially used, it was 350k in over 2 minutes... so almost 6x gain!

[thoandros]

WideBoyDixon hope you are ok - sorry to go back to this topic but I have a similar problem but I need your expertise to put it down as a code.

I have 15 values, let's say 15 cards. Card values are A to O and are in column A, cells 1 to 15.

Now, I have 5 players so I need to "distribute" the 15 cards, 3 per player.

I know that if the "position" of the card is important, I will have 15! possible ways. However, because the position of the card (among the 3 per player) does not matter, I know that then the possible ways to distribute the cards should be 15! / (5! x 10!) = 3003.

So, the macro should put in columns B to P, down for 3003 rows, all the possible distribution of cards. B/C/D columns are player 1, E/F/G are player 2 and so on.

What I mean is that if player 1 gets cards F G H, I don't need to see another row with G F H for example. Hope I am making sense and you can help me with this! thank you in advance!

[AliGW]

This is a completely new request. Please start a new thread with an appropriate title. Thanks.

[thoandros]

sure - my apologies!

[thoandros]

Adaptation of my earlier code:

Please Login or Register  to view this content.

WBD

Hi WBD, can you help me adapting the code above to consider just 9 columns (3x3) instead of 18? I tried changing some of the numbers but something is wrong. I need the very same, but in this case I have only 3 sets of 3 cells, rather than 6 sets. Thank you in advance!