Creating combinations with criteria

[sans]

Hello,

I am looking to create all combinations but each combination should only contain a certain amount of values from a column.

I am attaching an example sheet. Every combination should contain a total of 7 values. I hope this is possible.

Thank you for looking,
Sans

[StevenM]

Your "rules" for combinations are not clear. For example, you have "12" under column M with a 2 value and under column O with a one value. So does that mean "12" can be used 3 times? In addition, your combinations only show 7 columns (F-L), and yet you have five letters and four numbers, that would seem to imply that a combination could have nine items. Are you wanting to limit this to combinations with only 7 items? And are the first three letters and the last four number? I wrote a function which did something very similar just last week, and perhaps I could adapt it to your needs, if only I better understood your "rules" for making a combination.

[sans]

Hi Steven,
Thank you for your reply. Those are very good questions.

For example, you have "12" under column M with a 2 value and under column O with a one value. So does that mean "12" can be used 3 times?

Yes a same number can be used many times in a combination.

Are you wanting to limit this to combinations with only 7 items?

Yes, even though there are more items, I would like to limit the combinations to 7 items.

And are the first three letters and the last four number?

Not always, in some sheets, its only numers, and in some sheets mixed like the example (but when the text and numbers are mixed, they are not always in the same columns)

Thank you for your help,
Sans

[StevenM]

Two more questions: Are the letters always lowercase? And are the numbers always two digits?

[mrice]

Try this macro.

Please Login or Register  to view this content.

The headings need to be in row 1 and the possible values starting in row 3.

I've highlighted the section of code which removes the combinations with duplicate values (which is all of them in your sample data because of 12)

[sans]

I apologise for the late reply.

Yes the letters are always lower case, but the numbers might be one or two digits.

Thanks for the reply Martin, I'll test it now.

[sans]

That's superb Martin!!! It's exactly what I was looking for! I've tested it for a few hours with varying data and have not encountered any issues whatsoever. Simply amazing! And I don't think I have seen a bigger macro than this so far. Thanks a million for your help and time! Sans