reverse distribution of excel totals

[shawnp]

I have an Excel problem, that I need help with.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have the following table. I require 25 man days of work to be done at a given location. I calculate that I will need to make 10 visits to that location based on the frequency of my visits there. Therefore, known information is: 10 visits of different types to be carried out in 25 man-days)<o></o>
<o> </o>
<TABLE class=MsoNormalTable style="MARGIN-LEFT: -1.15pt; WIDTH: 214.35pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=286 border=0><TBODY><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>

Visit type<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>

Visits<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>

Man days<o></o>​

</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>1-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

1<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

1<o></o>​

</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>2-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

3<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

6<o></o>​

</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>3-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

6<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

18<o></o>​

</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>4-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

<o> </o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

<o> </o>​

</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>5-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

<o> </o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>

<o> </o>​

</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>Total<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>

10<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>

25<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>
Is it possible to devise a macro / Excel formula that automatically distributes the visit type and man days as shown, so that totals match i.e 25 man days work out to six 3-days visits + three 6-day visits + one 1-day visit? I just want to put 25 and 10 and get the visit distribution automatically.<o></o>
<o> </o>

 

[jbeaucaire]

Not without some included explanation the sheet can use giving the logic that is to be used to determine how many of each type of day to generate.

 

[shawnp]

Not without some included explanation the sheet can use giving the logic that is to be used to determine how many of each type of day to generate.

Actually, its the logic I need to generate. There could be more than 1 correct combination of visits, but the type of visits must total to 10 and the mandays must total to 25. The focus is to have a good visit distribution rather than just one type of visits (eg. all 1-day visits only)