how to generate all possible combinations of numbers using microsoft excel (ideallywithout macros)

I have got two groups of numbers.Group A: 1, 2, 3, 4, 5, 6, 7, 8; Group B: 9, 10, 11, 12, 13, 14, 15, 16. I would like to select 2 numbers and 4 numbers from group A and group B respectively, and there is no repetition in every possible combination of numbers. I was wondering whether I could have microsoft excel generate all possible combinations of numbers. Thanks a lot.
Tim
 

Question Info


Last updated September 10, 2019 Views 59,954 Applies to:

Hi,

 

You may refer to my solution at the following link - http://www.ashishmathur.com/create-all-possible-combinations-from-different-ranges-without-using-vba/

 

For any follow up questions, please post in the Comments section of the link shared above.

 

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

23 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks, Ashish. I have got data set 1 (1 to 8) and data set 2 (9-16). I would like to generate all possible combination of 6 numbers: in every possible combination, 2 numbers are from data set 1, and 4 numbers are from data set 2 and no repetition. If I apply the combination formulas (not permuatation), the number of all possible combinations will be [8!/(6!*2!)]*[8!/(4!*4!)=1960. I would like to have excel list all these possible 1960 combinations. In the file available in the following link, I list a few possible combinations in rows 13 to 19 for illustration purposes. Thanks. 

Tim

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi,

You may refer to my solution at the following link - http://www.ashishmathur.com/create-all-possible-combinations-from-different-ranges-without-using-vba/

For any follow up questions, please post in the Comments section of the link shared above.

Hope this helps.

I have used this with great success for 2 columns of data, but when I have tried to used the same solution and applied it to 3 columns of data it doesn't work. Is there a similar solution I can use to gain a list of all unique combinations from 3 datasets?

Thanks,

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi,

What problem are you facing?

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

This is very much helpful, I have used this with 6 data sets and gave me wonderful results.

Thanks Ashish.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

You are welcome.  If my reply helped, please mark it as Answer.
Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.