Pairing numbers from a list

[jms6497]

I have a list of 10 numbers (columns J7 thru S7) and I want to tabulate all of the combinations of 2 numbers or 3 numbers, etc. Any suggestions on how to do this?

[Chris Davison]

2 excellent suggestions here, from a similar earlier post

http://www.mrexcel.com/board/viewtop...c=5621&forum=2

[jms6497]

Chris, I tried the VBA solution from Tommy Bak....but it did not work. I will admit that I am a novice when it comes to VBA, so I may be doing something wrong. Is there not a "non-macro" solution to this problem??

[Chris Davison]

On 2002-04-29 12:59, jms6497 wrote:
I have a list of 10 numbers (columns J7 thru S7) and I want to tabulate all of the combinations of 2 numbers or 3 numbers, etc. Any suggestions on how to do this?

when you say "etc" do you mean "up to 10" ?

any permutation of 10 numbers from any 10 numbers is a possible 3,628,800 combinations
which may be difficult to tabulate given Excel's column and row limits of 255 and 65,536 respectively....

I'm not entirely sure a non-VBA method is possible, although I'd be very surprised if there is one....

[jms6497]

No....I mainly am interested in the groups of two numbers. But I sometimes will have a larger population of numbers to choose from (say 15 or 20), but I figured if I could figure out the 10 number procedure, I could apply it to a larger set of numbers.

[Chris Davison]

how about a simple 10x10 grid ?

with your population of 10 numbers along the x and y axes...

here's a simpler 5x5 example...

just copy the formula in the first cell over then down

(this assumes you want to add them)

	A	B	C	D	E	F	G	H	I	J
1	*	2	3	4	5	6	*	*	*	*
2	2	=+$A2+B$1	=+$A2+C$1	=+$A2+D$1	=+$A2+E$1	=+$A2+F$1	*	*	*	*
3	3	=+$A3+B$1	=+$A3+C$1	=+$A3+D$1	=+$A3+E$1	=+$A3+F$1	*	*	*	*
4	4	=+$A4+B$1	=+$A4+C$1	=+$A4+D$1	=+$A4+E$1	=+$A4+F$1	*	*	*	*
5	5	=+$A5+B$1	=+$A5+C$1	=+$A5+D$1	=+$A5+E$1	=+$A5+F$1	*	*	*	*
6	6	=+$A6+B$1	=+$A6+C$1	=+$A6+D$1	=+$A6+E$1	=+$A6+F$1	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
20	*	*	*	*	*	*	*	*	*	*

resulting in :

	A	B	C	D	E	F	G	H	I	J
1	*	2	3	4	5	6	*	*	*	*
2	2	4	5	6	7	8	*	*	*	*
3	3	5	6	7	8	9	*	*	*	*
4	4	6	7	8	9	10	*	*	*	*
5	5	7	8	9	10	11	*	*	*	*
6	6	8	9	10	11	12	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
20	*	*	*	*	*	*	*	*	*	*

[ This Message was edited by: Chris Davison on 2002-04-30 13:39 ]

[Jack in the UK]

chris that looks good if a shade wide.

well done nice!

[jms6497]

On 2002-04-30 14:07, Jack in the UK wrote:
chris that looks good if a shade wide.

well done nice!

I do not want to add the numbers, but this methodology may work with some modifications. Thanks for the help, guys!!