Page 1 of 1

Number in household?

Posted: Sun Oct 16, 2011 12:09 am
by fishyjim
I just spent 4 hours valuing the user defined field with the number of people in the household for our disaster response lists. Does anyone know a better way to accomplish this? MLS knows the individuals in each household, but I could not find a field that would count them for me... Any ideas would be appreciated.

Posted: Sun Oct 16, 2011 9:36 am
by jdlessley
There are a lot of calculations that could be derived from MLS data that are not done. This would just be one. If the built-in reports and the custom reports do not provide satisfactory results, you must be creative. Perhaps if you could explain why having the number of household members is needed then there may be another approach to getting the data you need.

Posted: Sun Oct 16, 2011 4:00 pm
by jonesrk
On thing I did to find the number of children was to run the Abbreviated Directory of Members and select show children of each household. Then I copied the data to Excel and on the children lines I used

(cell e48) =IF(C48="","",LEN(C48)-LEN(SUBSTITUTE(C48,",",""))+1)

basically that counts the commas and adds one. To get the whole family count you would need to check the parents for & to count it as one or two.

I actually put all of the family on one line with these two formulas
(cell b48) =IF(LEFT(A48,6)=" ","",A48) grabs the parents only
(cell c48) =IF(LEFT(A49,6)=" ",TRIM(A49),"") grabs the children from below the parents
I had just then names column from the report in column A.

Posted: Mon Oct 17, 2011 4:12 am
by kisaac
fishyjim wrote:I just spent 4 hours valuing the user defined field...
Welcome to the forum! I applaud your efforts, but question if it will be a sustainable effort after a few months of move-ins, or when you get released. I'm not saying "give up," I'm suggesting the more complex you make your system, the harder it will be to keep it going month after month, and year after year. If the house count is vital to you, and I can see the value of it for emergency uses, then perhaps JonesRK's spreadsheet calculations would help, but you still have HOURS of typing them back in to MLS.

Yes, MLS does know the individuals, and almost counts them in a field called HH Order (Household Order) when you do a membership export...almost!

We do our fast offerings routes using user defined fields. Works great, just takes time EVERY month to update the move ins, or it quickly becomes out-dated, and unusable. The advantage we have is we know when the fast offering day is, so I know when to update my routes. You won't know...

Posted: Mon Oct 17, 2011 12:21 pm
by ckellsworth
here is another option of generating the # of people in a household.

Get a report that is in the following format:
<head of household>, <household member>

You then can use Pivot tables in Excel to built a report that is:
<head of household>, <count of head of household>

attached is a quick proof of concept of how the input data should look, setup of the pivot table and how the output would look. The actual steps to do this will depend on what spreadsheet program your using. this screenshot was done using Neooffice, though i did test it in Excel.
Screen Shot 2011-10-17 at 10.49.38 .jpg
Screen Shot 2011-10-17 at 10.49.38 .jpg (23.34 KiB) Viewed 376 times

Posted: Mon Oct 17, 2011 2:04 pm
by kisaac
JonesRK wrote:On thing I did...
ckellsworth wrote:here is another option...
I will say a huge "thank you" to these posters and the many others here...this probably represents some real time and effort to research this question, and may be beneficial to many. I know I would never have thought of similar methods. These posters, and all others who spend time here helping others, is the true benefit of a forum like this.

Posted: Wed Oct 26, 2011 7:33 pm
by fishyjim
Thank you for these ideas - I think the pivot table is probably what I will look into next (I agree that the solution is most likely outside of MLS)... To answer the question earlier, our Stake is asking us to report number of individuals accounted for, so our block captains can simply note which households they visited, and we can let the spreadsheet do the counting/adding for us... Thanks again!