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,
I’m not sure if you want to either;
Completely remove the address, less the town name from the “Data” page.
OR
Show the Town name on the “Report” page in each row of consolidated data

Generally, it’s not a good idea to remove stuff from the source data, it is best to keep it and just extract what you need to show on the Report page.
If it is the first option, I would need to introduce a macro. If it is the second option we can add a column to the Data page to isolate the town name and then introduce a new column on the Report page to treat the Location as part of the consolidated data (this is the easier option).

If you are happy to use the second option, then on the Data page we can create a new column and use a formula. Based on your recent new header list, it would be column K
If you can be sure that there is no other text after the Town name AND the town name will always follow the word ”town: “AND there will never be words after the town name, then we can use the following approach:-

The sample text = AHS District Office (3 letter code): Jdo; county: Caledonia; town: St Johnsbury will be in column “I” and the first row of this type of data is in row 2 (Cell I2)

=Right (I2,12) will give the town name you need (because there are 12 characters in St Johnsbury, including the space) and we are only looking for the first 12 characters from the right.
But 12 is a variable number of characters, so we need to count how many characters are in each of the town names

=LEN(I2) gives the total number of characters in the sentence = 79
=FIND(“Town: “) gives the number of characters up to the start of the word town: = 62
The word town: has 5 characters (add this to the 62 to get to the start of St Johnsury) = 67
We can subtract 79 – 67 = 12 (12 is the variable figure we need).

So the first formula of =Right(I2,12) can be re-written as:-
=RIGHT(I2,LEN(I2)-FIND("town: ",I2)-5) this formula can be put in K2 on the Data Page and copied down and will provide a list of just the town names.

It won’t actually work because the Location column does not exist on the sample workbook yet but I can add it later tonight.
Would this approach be acceptable?

Also
you kindly gave me the real header titles for the Data page, could you do the same for the Report page please. I can manually add the Location column but it would assist if you show in what order you would like to see it.

Cheers
Paul.

 

[Taul]

Hi Beth,
I think I have answered my own question and I will assume we will not delete data from the source. I have added a few extra reports on this file.
If you want to remove report 1, then we can also remove the UDF and save the file as xlsx format.
Take a look and let me know if it provides what you need or if you need any tweaks.

Forum Q - Rev2.xlsm

Shared with Dropbox

www.dropbox.com

cheers
Paul

 

[VtQuest]

Hi Paul,
I'm sorry that I only saw your great suggestions and reports today - very busy on Monday.

I like what you have suggested and only have one other header to add to Report 1: "Criteria" -- the original language is in parentheses. I only need Exact Age, if that makes a difference.

Client ID

Registered Date

Date of Birth

Cause of Homelessness

Criteria (Choose ALL of the criteria that applies to client (if ANY))

Group/individual Type

Housing authorized?

# of Kids

# of nights

Approx Age

Exact Age

Occurrence

Town

The one element I will have to add is more choices among the categories of Cause of Homelessness, Criteria, Group/Individual Type. In working with my data to convert phrases to the short codes you recommended, I came upon other choices and added more short codes:

I will add these codes to the categories set up in Reports 2, 3, and 4 if doing so won't cause a problem.

Many thanks!

Beth

 

[VtQuest]

Hi Paul,

A quick question. I see the formula that you added to the Data tab. Is it ok to paste my actual data into that sheet. It won't overlap and delete the formula for "Town," so it should be ok. I am assuming that is so.

Thank you,
Beth

 

[Taul]

Hi Beth,
Quote:- "In working with my data to convert phrases to the short codes you recommended"
Please do not change your raw data, the long words can remain in the source data, the short codes are used only in the report. Take a look at the notes for Report 3 to see how it is done.

cheers
Paul

 

[Taul]

Hi Beth,
Quote "A quick question. I see the formula that you added to the Data tab. Is it ok to paste my actual data into that sheet. It won't overlap and delete the formula for "Town," so it should be ok. I am assuming that is so."

Yes, that is ok

 

[VtQuest]

Hi Paul,

I see , thank you. Can I add the additional code headings to Reports 2-4? E.g., "Single female with child(ren)"?

Beth

 

[Taul]

Hi Beth,
Yes, go ahead, if you hit a problem let me know.
The new column called "Criteria" on the Data page only exists on the Data page, it is not in any of the reports. Does it need to be?

cheers
Paul.

 

[VtQuest]

Hi Paul,

I meant to show where I would add the new choices:

I wouldn't add the new choices in the total row, but insert new rows for those choices.

Please let me know if this is ok.

Thank you,
Beth

 

[VtQuest]

Yes, the new column for "Criteria" would also need to be added to the reports. Is that possible?