Permutation & Combination Problem in Excel for Creating Payment Groups

[wouldbeca]

Input Data:

I have a data table with 3 columns:

• Supplier No.
• Location No.
• Payable/(Receivable Amount

Example of a sample part of Input data is attached

Output Data required:

In order to process payment to several suppliers every day, I need to create “payment groups” comprising of Supplier Nos and Location Nos in our accounting software.

Conditions

Please help me with excel formula/VBA code, to determine the best possible combination sets of Supplier No. and Location ID for creating “payment groups” with the below conditions:

• No. of payment groups prepared is minimum.
• Maximum 10 No. of location IDs can be used in each group.
• Net grouped amount of each and every vendor in any payment group is positive.

In case of any further query, please revert.

Book4
	A	B	C
1	Supplier No.	Location No.	Amount Payable/-Receivable
2	27029	110	7,36,654
3		111	15,78,320
4	27094	112	6,62,450
5	27090	113	32,11,385
6	27069	114	2,72,908
7	27026	114	14,27,552
8	27071	115	2,62,161
9	27094	116	-512
10		117	2,66,339
11		118	-12,278
12		119	-3,070
13		120	15,56,969
14	27034	117	72,552
15		120	1,63,404
16	27059	120	1,09,069
17		121	18,431
18		122	1,26,463
19	27041	123	18,22,797
20	27095	123	2,14,332
21		124	1,14,217
22	27012	125	3,95,874
23	27062	112	2,54,339
24	27010	115	9,40,853
25	27093	126	1,78,133
26	27043	127	98,669
27		123	1,17,560
28	27052	127	97,295
29		123	59,452
30	27057	127	33,101
31		123	1,17,027
32	27016	127	1,64,141
33	27004	127	1,29,272
34		123	1,17,494
35	27081	127	32,842
36		123	2,35,920
37	27095	127	98,447
38		123	1,17,627
39	27003	127	1,29,468
40		123	1,17,560
41	27012	127	33,816
42		123	1,17,828
43	27023	127	1,31,882
44	27098	127	1,32,013
45	27066	127	1,05,635
46	27067	127	1,64,940
47	27088	127	33,816
48		123	1,17,227
49	27055	123	1,18,094
50		128	-2,444
51	27071	129	-1,000
52		130	54,177
53	27047	131	4,854
54		132	2,81,784
55	27053	133	6,549
56		132	11,54,029
57		134	18,755
58	27054	132	1,22,018
59	27055	133	8,826
60		132	4,17,359
61	27066	112	10,08,036
62	27087	112	5,49,803
63	27012	112	8,70,079
64	27079	127	33,160
65		123	1,17,828
66		128	-977
67	27078	135	72,300
68	27037	136	-2,08,524
69		137	5,11,147
70		112	77,901
71	27038	136	-1,37,044
72		137	-20,005
73		112	28,934
74	27053	137	3,15,715
75	27076	111	1,32,996
76	27077	111	2,46,981
77	27085	113	7,51,573
78	27097	138	2,25,628
79	27007	138	5,29,166
80	27008	138	4,72,631
81	27050	139	1,73,094
82	27051	139	5,47,820
83	27001	115	7,12,246
84	27010	115	3,66,801
85	27040	115	4,74,174
Sheet1

 

[jdellasala]

Everyone thinks the only solution to any problem is VBA. Learn Power Query! Playlists here and here. Thank you for using XL2BB though!
Preamble - changing data's orientation (L/R/C) should only be done when needed. If left alone, numbers will always justify right, and text will always justify left. It's a powerful tool to tell what's going on with your data, and never format money without two digits if it's just data. Your 3rd column is actually Decimal Numbers. That said, all 3 columns of your sample are numbers, and the original data has 84 columns, and there are only 82 unique combinations of the first and second column! Got that with Power Query in about a minute - an identical table with 82 rows, and the rows where both Column 1 and 2 were the same added together.
I was able to take the results of that table and subtotal column 3 resulting in two columns - one for each supplier with the totals for each supplier in the 2nd column - 45 suppliers.

If you pull that data in from a file, you could create a master to pull in the data, transform it as needed, have completed, updated tables in a couple (not even a few) minutes. Here's what I got:

Excel VBA User Defined Function To Generate A Dynamic Array - 2516.xlsm
	E	F	G	H	I	J
1	Supplier No.	Location No.	AP/R Totals		Supplier No.	AP/R by Supplier
2	27029	110	736,653.85		27029	2,314,973.84
3	27029	111	1,578,319.99		27094	2,469,899.26
4	27094	112	662,450.29		27090	3,211,384.73
5	27090	113	3,211,384.73		27069	272,907.81
6	27069	114	272,907.81		27026	1,427,552.26
7	27026	114	1,427,552.26		27071	315,337.49
8	27071	115	262,160.83		27034	235,955.85
9	27094	116	-511.60		27059	253,962.21
10	27094	117	266,339.36		27041	1,822,797.21
11	27094	118	-12,278.40		27095	544,622.15
12	27094	119	-3,069.60		27012	1,417,597.09
13	27094	120	1,556,969.21		27062	254,339.03
14	27034	117	72,551.56		27010	1,307,653.85
15	27034	120	163,404.29		27093	178,132.88
16	27059	120	109,068.60		27043	216,229.36
17	27059	121	18,430.96		27052	156,747.29
18	27059	122	126,462.65		27057	150,128.20
19	27041	123	1,822,797.21		27016	164,141.47
20	27095	123	331,958.38		27004	246,766.67
21	27095	124	114,217.25		27081	268,762.02
22	27012	125	395,873.63		27003	247,027.94
23	27062	112	254,339.03		27023	131,882.17
24	27010	115	1,307,653.85		27098	132,012.84
25	27093	126	178,132.88		27066	1,113,670.41
26	27043	127	98,669.38		27067	164,939.67
27	27043	123	117,559.98		27088	151,043.83
28	27052	127	97,295.11		27055	541,836.22
29	27052	123	59,452.18		27047	286,638.44
30	27057	127	33,101.36		27053	1,495,047.86
31	27057	123	117,026.84		27054	122,018.12
32	27016	127	164,141.47		27087	549,803.29
33	27004	127	129,272.21		27079	150,010.87
34	27004	123	117,494.46		27078	72,299.95
35	27081	127	32,841.84		27037	380,524.36
36	27081	123	235,920.18		27038	-128,115.25
37	27095	127	98,446.52		27076	132,996.21
38	27003	127	129,467.96		27077	246,980.73
39	27003	123	117,559.98		27085	751,572.54
40	27012	127	33,816.43		27097	225,628.32
41	27012	123	117,828.06		27007	529,166.28
42	27023	127	131,882.17		27008	472,630.89
43	27098	127	132,012.84		27050	173,094.48
44	27066	127	105,634.50		27051	547,819.99
45	27067	127	164,939.67		27001	712,246.26
46	27088	127	33,816.43		27040	474,174.38
47	27088	123	117,227.40
48	27055	123	118,094.12
49	27055	128	-2,443.50
50	27071	129	-1,000.00
51	27071	130	54,176.66
52	27047	131	4,854.02
53	27047	132	281,784.42
54	27053	133	6,549.15
55	27053	132	1,154,028.89
56	27053	134	18,754.96
57	27054	132	122,018.12
58	27055	133	8,826.17
59	27055	132	417,359.43
60	27066	112	1,008,035.91
61	27087	112	549,803.29
62	27012	112	870,078.97
63	27079	127	33,160.21
64	27079	123	117,828.06
65	27079	128	-977.40
66	27078	135	72,299.95
67	27037	136	-208,523.75
68	27037	137	511,147.10
69	27037	112	77,901.01
70	27038	136	-137,044.12
71	27038	137	-20,004.71
72	27038	112	28,933.58
73	27053	137	315,714.86
74	27076	111	132,996.21
75	27077	111	246,980.73
76	27085	113	751,572.54
77	27097	138	225,628.32
78	27007	138	529,166.28
79	27008	138	472,630.89
80	27050	139	173,094.48
81	27051	139	547,819.99
82	27001	115	712,246.26
83	27040	115	474,174.38
Sheet3

Power Query M Code for the first table:

let
    Source = Excel.CurrentWorkbook(){[Name="tblPmts"]}[Content],
    FilledDown = Table.FillDown(Source,{"Supplier No."}),
    ChangedType = Table.TransformColumnTypes(FilledDown,{{"Supplier No.", Int64.Type}, {"Location No.", Int64.Type}, {"Amount Payable/-Receivable", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Supplier No.", "Location No."}, {{"AP/R Totals", each List.Sum([#"Amount Payable/-Receivable"]), type nullable number}})
in
    GroupedRows

For the second table (which uses the results of the first query/table):

let
    Source = tblPmts,
    GroupedRows = Table.Group(Source, {"Supplier No."}, {{"AP/R by Supplier", each List.Sum([#"AP/R Totals"]), type nullable number}})
in
    GroupedRows

EZPZ!