Pivot data returning earliest date and latest date (only) counts by a training class number

[SerenityNetworks]

I need to generate a Pivot Table and Chart. I believe standard Pivot Table functionality is not up to the task, and I'll need Power Pivot. I'm okay with either, but I need guidance. I do need to avoid using summary columns in/beside the source data if at all possible.

I assist an organization that helps the needy. The organization conducts 9-week classes. A student's status is tracked over time for such things as do they have transportation, what is their housing status, do they have employment, do they have insurance, etc. I would like to generate a set of Pivot Tables and Charts that will show, by class number, client status counts upon entering the program (i.e. the earliest update date for the client) and their latest status (i.e. the latest update date for the client). Update information that falls between the earliest and latest dates will be ignored. For example, if there are status updated made 1/1, 2/1, 3/1, and 6/1, then the 2/1 and 3/1 updates are ignored. Note: client information is most commonly not updated on the same dates.

The source data would look something like this:

Class	Name	Updated	Housing	Transportation
45​	Aaron	1/1/2023​	Homeless	No
45​	Aaron	2/1/2023​	Apartment	No
45​	Aaron	6/1/2023​	Apartment	Yes
45​	Bob	1/1/2023​	Homeless	No
45​	Bob	2/1/2023​	Incarcerated	N/A
45​	Charlotte	1/1/2023​	Apartment	No
45​	Charlotte	1/2/2023​	Apartment	Yes
45​	Charlotte	3/1/2023​	Apartment	Yes
46​	Dawn	6/1/2023​	Homeless	No
46​	Dawn	7/1/2023​	Apartment	No
46​	Dawn	10/1/2023​	Apartment	Yes
46​	Eric	6/2/2023​	Apartment	Yes
46​	Eric	7/1/2023​	Apartment	No
46​	Eric	12/1/2023​	Apartment	Yes
47​	Frank	6/1/2023​	Homeless	No
47​	Frank	8/1/2023​	Homeless	Yes
47​	Gina	6/1/2023​	Apartment	No
47​	Gina	7/1/2023​	Apartment	No
47​	Gina	8/1/2023​	Apartment	Yes
47​	Gina	1/1/2024​	Home	Yes
47​	Henry	6/3/2023​	Apartment	No
47​	Henry	7/1/2023​	Homeless	Yes
47​	Henry	10/1/2023​	Apartment	Yes
47​	Henry	2/2/2024​	Home	Yes

The results table would be something like the following (first row added for clarity on what's being returned in the second row and below):

		Housing	Housing	Housing	Housing	Transportation	Transportation	Transportation
Class	Update	Homeless	Apartment	Incarcerated	Home	Yes	No	N/A
45	Earliest	2	1	0	0	0	3	0
45	Latest	0	2	1	0	2	0	1
46	Earliest	1	1	0	0	1	1	0
46	Latest	0	2	0	0	2	0	0
47	Earliest	1	2	0	0	0	3	0
47	Latest	1	0	2	0	3	0	0

Right now, I'm at a loss as how to limit the results table to only the data needed/shown. I've tried using standard pivot functionality using Class as a Row field, Update as a Row field filtered by value, Housing and Transportation as a Column field, and Transportation as a Value field set to Count. That's not getting me what I need - at all.

I need some guidance with as much detail as you have time to provide. I'm not well acquainted with Power Pivot.

Thanks,
Andrew

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Pivot data returning earliest date and latest date (only) counts by a training class number - OzGrid Free Excel/VBA Help Forum

I need to generate a Pivot Table and Chart. I don't know for sure whether this can be accomplished with regular Pivot functionality or if it needs Power Pivot. I'm okay with either, but I need guidance. I do need to avoid summary columns in the source…

forum.ozgrid.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[SerenityNetworks]

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks Rory. I actually just made an update on OzGrid and will be making an update post here too. I don't want to waste anyone's time.

 

[SerenityNetworks]

Updates:

1. Having not received the start of any discussion on a solution, I cross-posted here. As of now, I have not received a response there either.
2. More importantly, I am currently working on a potential solution using the index and match functions on the source table that returns the summary/results data I need to generate a Pivot Chart. It's looking promising. Please, post or send me a message if you believe you have a Power Pivot solution before spending any time on it. I'll let you know how I'm going with my own solution. Of course, I'll post back here if I do succeed. I don't want to waste anyone's time.

 

[Sam_D_Ben]

Hi,

How did you get "1" in Class 45, Earliest update Apartment column and "2" for the same class Latest update Apartment column.

but I am getting Earliest "2" and Latest "1" for the same.

Pivot data returning earliest date and latest date (only) counts by a training class number.xlsx
	T	U	V
3			Housing
4	Class	Update	Apartment
5	45	Earliest	2
6	45	Latest	1
Sheet5

Cell Formulas
Range	Formula
V5	V5	=COUNTIFS($K$3:$K$26,MINIFS($K$3:$K$26,$J$3:$J$26,$B$4,$I$3:$I$26,$V$4),$J$3:$J$26,$B$4,$I$3:$I$26,$V$4)
V6	V6	=COUNTIFS($K$3:$K$26,MAXIFS($K$3:$K$26,$J$3:$J$26,$B$4,$I$3:$I$26,$V$4),$J$3:$J$26,$B$4,$I$3:$I$26,$V$4)

Thanks

 

[SerenityNetworks]

Thanks for taking a look at my problem.

How did you get "1" in Class 45, Earliest update Apartment column and "2" for the same class Latest update Apartment column.

• For earliest:
  • Only Charlotte was in an apartment when she started Class 45. Aaron and Bob were homeless when they started Class 45. So for the earliest information on the three in Class 45, only one was in an apartment (Charlotte).
• For latest:
  • When Aaron was last checked on (6/1/23), he was in an apartment. Bob was incarcerated when last checked on. Charlotte was in an apartment when she was last checked on (3/1/23). So on the last updates for the three that were in Class 45, there were two in apartments.

But I am having trouble seeing how you have structured your functions. In what range did you paste the source data?

Thanks,
Andrew

 

[Sam_D_Ben]

But I am having trouble seeing how you have structured your functions. In what range did you paste the source data?

Please ignore it, i was trying to pull out Earliest and the Latest.

Thanks

 

[Sam_D_Ben]

I like to to know what format is your source file. Is it text ?

Thanks

 

[SerenityNetworks]

I like to to know what format is your source file. Is it text ?

It is Excel. The source data is an Excel tracking workbook.

But I do already know how pull Earliest and Latest values. Getting the data in a Pivot is my issue. Yet please be aware that I did cross post and one of the Admins at OzGrid has started helping too. He posted a sample with some instructions here. You might want take a gander.

Thanks,
Andrew

 

[RoryA]

I posted it there because it's easier to use an attachment for things like this.