How to find the best groupings - highest combined scores

How do you define 'highest connectivity'? A simple sum?

That's not the 150 in your example.

No idea at all? Within the sample set you provided, what solution would you find? Short but useless answer, yes, I expect that something like this is possible using the programming tools available to us (formulas, Solver, and/or macro). However, without some idea of what the logic or algorithm looks like, it will be difficult to actually write the program.

How about this wild guess:
1) identify the 5 largest values in the matrix (could use the LARGE() function). (44.3, 21.3, 20.6, 20.4, 15.9)
2) Find those 5 values in the matrix and identify the group pairs associated with those values. (5-4, 4-3, 5-3, 7-6, 2-1)
3) Record the groups and sum the values. (sum=122.6)

That's almost certainly wrong, but does it give you a framework to talk about the algorithm you really want to use?

Does a solution to this problem have a practical application? If so, would you please describe?

Ah, interesting.

It's easy enough to do brute force (there are only about 75M combinations to evaluate), the code is just fussy.

Here's the result for a random 50-group set.

	BD	BE	BF	BG	BH	BI	BJ
5	Conn	Size	Grp ...
6	52.424	293	46	45	23	17	9
7	52.022	330	43	25	18	14	12
8	51.986	338	49	39	25	18	12
9	51.853	338	42	25	18	13	12
10	51.675	326	33	29	20	19	6
11	51.507	334	45	23	17	9	6
12	51.310	324	46	45	17	9	6
13	51.136	265	46	45	44	22	9
14	51.010	291	41	33	31	14	4
15	50.380	312	46	45	43	34	9
16	50.329	319	33	25	18	14	12
17	50.195	305	41	40	33	31	14
18	49.961	291	39	25	23	18	12
19	49.949	323	25	24	23	18	12
20	49.877	381	49	45	17	9	6
21	49.661	319	25	18	16	14	12
22	49.553	296	42	25	23	18	12
23	49.521	395	49	20	19	17	6
24	49.491	321	42	25	18	12	10
25	49.398	295	31	25	18	14	12

It took about 15 seconds, so a 100-group set should take about 10 minutes. Workbook attached.

A little performance enhancement.

Make the data square as in your original post. If there is zero connectivity, so be it.

There's a big difference between 100 and 149 items; it will run for over an hour.

Yes .

Sorry to bother you some more. I did as suggested and I am getting a run time error at about 3.7%.

Can you advise on what to do? I have attached the data with the modifications.

That ran for 42 minutes while I was downstairs eating lunch to produce this result:

	EY	EZ	FA	FB	FC	FD	FE
4	Conn	Size	Grp ...
5	450.262	358	141	27	26	24	22
6	392.160	398	141	37	26	24	22
7	384.706	375	141	42	26	24	22
8	381.966	393	141	26	25	24	22
9	360.497	298	37	27	26	24	22
10	352.798	449	141	48	26	24	22
11	352.725	275	42	27	26	24	22
12	352.035	477	141	36	26	24	22
13	351.248	538	141	94	26	24	22
14	351.105	442	141	100	26	24	22
15	350.948	372	141	38	26	24	22
16	350.942	436	141	131	26	24	22
17	350.845	510	141	56	26	24	22
18	350.614	372	141	55	26	24	22
19	350.441	402	141	40	26	24	22
20	350.288	417	141	128	26	24	22
21	350.127	386	147	141	26	24	22
22	350.024	398	141	133	26	24	22
23	349.999	405	146	141	26	24	22
24	349.859	399	141	26	24	22	3
25	349.841	391	141	26	24	22	11
26	349.795	355	141	136	26	24	22
27	349.758	559	141	31	26	24	22
28	349.712	513	141	32	26	24	22
29	349.682	374	141	72	26	24	22
30	349.632	453	141	26	24	23	22
31	349.630	440	145	141	26	24	22
32	349.600	366	141	79	26	24	22
33	349.591	458	141	50	26	24	22
34	349.567	405	141	111	26	24	22
35	349.525	453	141	35	26	24	22
36	349.518	384	141	62	26	24	22
37	349.514	367	141	118	26	24	22
38	349.513	397	141	103	26	24	22
39	349.491	407	141	67	26	24	22
40	349.471	380	141	26	24	22	2
41	349.468	598	141	98	26	24	22
42	349.461	393	141	26	24	22	16
43	349.460	404	141	26	24	22	17
44	349.444	375	141	75	26	24	22
45	349.442	465	141	26	24	22	18
46	349.441	382	141	43	26	24	22
47	349.430	455	141	107	26	24	22
48	349.430	395	141	88	26	24	22
49	349.422	399	141	74	26	24	22
50	349.421	393	141	78	26	24	22
51	349.421	380	141	135	26	24	22
52	349.416	397	141	101	26	24	22
53	349.412	392	141	104	26	24	22
54	349.411	397	141	28	26	24	22

The code was slightly different than posted, but the changes were generally cosmetic; nothing that should have affected functionality.

Please Login or Register  to view this content.

Originally Posted by Daniel777

Hi Shg, is there a way to make the groupings exclusive? ...

Software can do anything, so yes, but that's different than what you asked for.