Consolidate Data, Combine Text, and Calculate Age

[VtQuest]

I am working with a spreadsheet regarding housing for homeless people. The people are represented by ID numbers. There are many duplicates. I want to sum SOME of the values associated with the IDs while also combining text notes. The values I wish to sum are Number of Nights. I wish to combine the text in the Causes column and in the Housing Authorized column (separately). I’d also like to calculate age from birthdates, and number of calls based on call date. (Call date is not listed in the first example.)

I have familiarity with Excel in its simple form, not at this level. (I’m sure, to some people, this is pretty simple, but not to me - yet.) So, I’m not familiar with many formula terms or query tools. Please respond with this in mind.

The first image is a sample of the data, with identifying information obscured. The second image is of the desired result.

Thank you for your help.

 

[Taul]

Hi Beth,
Ok, I will add the new headings to the report, it also gives me a chance to tidy it up a little.

cheers
Paul

 

[Taul]

Hi Beth,
The header is "Criteria - Choose ALL of the Criteria that applies to client (if ANY)"

I need to know what the data would look like, I assume they might be a set of text strings.
Could you post some typical data for this column please?

cheers
Paul

 

[VtQuest]

Here's a sampling of what the data would look like in this column:

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC); VULNERABLE POPULATION - recipient of SSI or SSDI

Adverse Weather Conditions (AWC)

VULNERABLE POPULATION - recipient of SSI or SSDI

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

CATASTROPHIC - domestic violence

CATASTROPHIC - domestic violence

Adverse Weather Conditions (AWC); VULNERABLE POPULATION - recipient of SSI or SSDI

Points - Enter number of eligibility points (0, 1,2, 3 or 4): 2

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

VULNERABLE POPULATION - recipient of SSI or SSDI

Adverse Weather Conditions (AWC); VULNERABLE POPULATION - recipient of SSI or SSDI

Adverse Weather Conditions (AWC)

Adverse Weather Conditions (AWC)

 

[VtQuest]

Hi Paul,

There are more choices than those shown above. The "Points" have been shortened to Points: number. I'd rather not have to put back in the original language, as it took a while to reduce it to the word and number.

Thank you,
Beth

 

[Taul]

ok, thanks for that.
I think this one was missing from the list, I will add it

"Points - Enter number of eligibility points (0, 1,2, 3 or 4): 2"

 

[VtQuest]

Yes, with that one, I changed the column to "2 Points" etc. I did that manually -- I'm sure there was an easier way, but I thought that would work better. I'd rather just keep it to the shorter version unless there's a workaround.

 

[VtQuest]

Just to clarify: There were many, many rows with that "Points - Enter number of eligibility points (0, 1,2, 3 or 4): 2" except the last number varied from "0" to "5."

 

[VtQuest]

Here are all of the choices for the "Criteria" column (in addition to the Points selection):

ADVERSE WEATHER CONDITIONS (AWC)
CATASTROPHIC - court ordered / constructive eviction
CATASTROPHIC - domestic violence
CATASTROPHIC - Natural disaster
VULNERABLE POPULATION - 3rd trimester of pregnancy
VULNERABLE POPULATION - child 6 or under
VULNERABLE POPULATION - recipient of SSI or SSDI
VULNERABLE POPULATION -65+

 

[Taul]

Hi,
Sorry for the delay but I have added the Criteria as requested. I hope I have got it right based on the details provided.
Let me know it it needs tweaking.
cheers
Paul.

Forum Q - Rev3.xlsm

Shared with Dropbox

www.dropbox.com

 

[VtQuest]

Hi Paul,

Thank you for adding the additional information. I hope to work on this today and will be back in touch tomorrow. I really appreciate all of your patient help!

Thank you,
Beth