Excel - group cells that the values together is near to a number

Question

I have this spreadsheet:

Street 1 | 234m street 2 | 438m Street 3 | 759m Street 4 | 934m Street 6 | 126m Street 7 | 462m

These values are summed and divided by 2 in this example, because let's say two people need to go through the same distance of these streets (no one of the two people want to work more).

Summed value: 2953m Distance for each people: 2953 / 2 = 1476,5m

How to group those streets that if we summing up one of their group's value, it will be the nearest option to 1476,5m?

This is a much bigger excel table with much more streets and people.

Thank You for your help!

Answer 1

I would use the Power Query Add-In for this. I had a go at this particular challenge and got it working. You can download and use my demo file from my OneDrive:

http://1drv.ms/1AzPAZp

It's the file: Power Query demo - Group rows into sets of similar total value.

As described on the ReadMe sheet, I didn't have to write many functions - it was mostly built by clicking around the UI.

My design is to calculate each row's variance from the average Distance, then Rank on that, then split the rows by Rank into 2 alternate groups. I used the Modulus function, so you could change that to produce 3 or more groups as required.