Has income of homeless improved/worsened/stayed the same after our Org has seen them.

[jonmagee]

This will probably be a pretty involved process, but was wondering if you guys had any suggestions. I work for a homeless organization and we used a shared database between the other homeless service providers. I have an excel sheet I pulled from the database that lists all of the income amounts of different homeless people as each organization encounters them. I'm trying to find a way to figure out what was each client's end result (their last income recorded) financially (better,worst, or the same) AFTER they came to our organization (our organization is "HRS"). I've attached the excel sheet, but also will provide a diagram and some examples and what their end result should be for the specific client:

1st Column = The client
2nd Column = The date when the income was recorded for the client
3rd Column = The amount of income the client made
4th Column = The organization that recorded the income (our's is "HRS")

Examples:

18572 2/01/2013 12:00 AM 100 SFH
18572 2/28/2013 12:00 AM 200 HRS
18572 8/13/2013 12:00 AM 150 OPD
18572 8/13/2013 12:00 AM 210 OPD
This would count as improved for the client, because the last income recorded (210) is higher than when they saw us (200)


18644 4/30/2013 12:00 AM 200 HRS
18644 9/17/2013 12:00 AM 400 HRS
18644 9/17/2013 12:00 AM 100 HRS
18644 9/17/2013 12:00 AM 150 HRS
This would count as worsened, as the first income we recorded was 200, but the last income recorded was 150.


18657 3/14/2013 12:00 AM 100 HOT
18657 3/14/2013 12:00 AM 200 HOT
18657 7/8/2013 12:00 AM 300 HRS
This would count as N/A, because there hasn't been income recorded since we recorded it.


18671 3/16/2013 12:00 AM 200 HRS
18671 3/19/2013 12:00 AM 300 HOT
18671 3/20/2013 12:00 AM 400 HRS
18671 3/28/2013 12:00 AM 200 NHS
This would count as "stayed the same", because the first time we saw them we recorded 200, and the last income recorded is 200.

[hemesh]

Hello there are some client's who are not recorded by your organization like client 5894 and One more thing as per of your example first income is what has been recorded by your organization and last income is the last time income recorded by any organization. and you are expecting results as last income - first income

Please correct me if i am wrong

[jonmagee]

If a client doesn't have anything recorded by our organization, they could count as "N/A" as well. There should just be a few clients like that.

[hemesh]

Ok ! Got your point. Please clarify One more thing as per of your example first income is what has been recorded by your organization and last income is the last time income recorded by any organization. and you are expecting results as last income - first income

Please correct me if i am wrong

[jonmagee]

Yes, first income is the first income that we have recorded. Last income can be by anyone (us included). I need one result for each client, so there are 1506 clients on that report; I need the results to be either "improved", "worsened", "same" or "N/A", and they would total 1506 results. Hope this helps. Thanks so much for looking into this!

[hemesh]

Hello jonmagee , Find the attached.
I have done it in two way first with helper column and other with one formula.

Some formulas used are array formula(To enter formula as array first select the cell of formula press f2, then hold control and shift together then hit enter) once a formula is entered as array you can see formula is surrounded by curly braces {}.

[jonmagee]

Thanks so much for putting this together for us, I know it's taken a lot of time. There is one thing that is still not reporting right though; clients that only were reported by us (but that have more than one report) should count. For instance:

5960 7/2/2012 12:00 AM 674 HRS
5960 5/6/2013 12:00 AM 820 HRS
This should count as improved, as they did improve after we first saw them.

Does that make sense?

[hemesh]

Not an issue ! in D2 Try this (Helper column Way sheet
=IF(AND(B3>=1,C3>1),1,NA()) and drag down ! See if this you want
if thats the same i will change One cell formula accordigly

[jonmagee]

That's working! That is awesome!!

[hemesh]

Hello, I have changed the formula in both the sheets. Just have a look

[jonmagee]

Thanks! That works perfect. What a life saver :D

[hemesh]

you are welcome

[jonmagee]

I'm going to be running that report once a month, what would the proper steps be to take my initial list and apply the formula's to it?