Divide and Distribute Costs to Multiple Participants Based on Individual Amount Owed.

I am trying to create a sheet that will properly distribute proportional costs of a vacation to all families. Throughout the vacation, random family members pay for various group items such as rentals, gas, meals, etc. I have been able to calculate how much each family has paid and how much each family owes.

I need to figure out how to divide the amount one family owes and pay it to other families that are owed. To reduce the number of checks that are writen, it would be best to have the family OWING the most pay the family who is OWED the most.

In the example below, Families 1 & 5 are owed money. Families 2 & 3 did not attend and therefore owe nothing. Families 6 through 10 owe varying amounts to the other two families. EACH VACATION HAS A VARIABLE NUMBER OF FAMILIES, FAMILIES OWED AND FAMILIES OWING.

It would be best to have the family OWING the most, pay the family OWED the most and then continue to the next family. As such, Family 10 owing 509.00 would pay Family 5, then Family 6 woud pay, then Family 7 pay, then Family 9 would pay the balance, with the remainder paid to Family 1.

I want to display results in a grid like shown below.

How can this be accomplished?

Thanks in advance for your efforts and insights!
Rog




Please Pay -> Family1 Family2 Family3 Family4 Family5 Family6 Family7 Family8 Family9 Family10
Family1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family6 0.00 0.00 0.00 0.00 0.00 342.00 0.00 0.00 0.00 0.00
Family7 0.00 0.00 0.00 0.00 0.00 294.00 0.00 0.00 0.00 0.00
Family8 71.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Family9 131.00 0.00 0.00 0.00 0.00 163.00 0.00 0.00 0.00 0.00
Family10 0.00 0.00 0.00 0.00 0.00 509.00 0.00 0.00 0.00 0.00


Family1 Family2 Family3 Family4 Family5 Family6 Family7 Family8 Family9 Family10 TOTALS
Total Paid 300.00 0.00 0.00 0.00 1,700 50.00 0.00 125.00 0.00 275.00 2,450.00 Total Spent
Family Members 1 0 0 0 2 2 2 2 2 4 15 Total Vacationers
Family Days 1 0 0 0 4 4 3 2 3 8 25 Total Family Days
Cost Per Family 98.00 0.00 0.00 0.00 392.00 392.00 294.00 196.00 294.00 784.00 98.00 Daily Cost per Vacationer
Due (Owed) (202) 0.00 0.00 0.00 (1,308) 342.00 294.00 71.00 294.00 509.00 0.00