Narrowing down a database to a simple spreadsheet report

[allynj]

Hi, this forum has been extremely helpful to someone with limited Excel skills.
I've managed to create a database of hundreds of people who have taken anywhere from 1-5 required classes on a multiple of dates. The classes are given repeatedly over the course of the year. This is something that is ongoing that will be updated. The data is exported from cvent into excel files that I cut and paste into a data sheet.

The funder (who has limited excel skills as well) wants a simple spreadsheet showing who has taken the classes on what dates.

The funder does not want to read or look at pivot tables or charts.

I have a pivot table that counts when people took the class. I need to make the spreadsheet smaller and shorter. I've attached a sample of what I have, and what I need it to look like. I am not sure what forumla or function to use. Vlookup?

Ack...

[mikeTRON]

Why does he/she not want to look at pivot tables?

I think they can be EASIER to digest than an actual table since it automatically groups and colors things appropriately.

[allynj]

I was just told that the funder is the client, and they want it a certain way, and that's that. This is a new job for me and I'm not in a position to argue otherwise.

I just have to create it so that they can read it, all they want to be able to do is see the print out, and perhaps filter and sort. No one in my office knows what pivot
tables are either. I tried showing them, and the just want me to cut and paste the results into a table that isn't a pivot. And pare it down.

[mikeTRON]

I would do both, and show them why a pivot table is easier, but I am hardheaded. Plus a Pivot table will automatically update to include new names and classes where this flat table and summary will not.

Can you simplify the data at the top to list (it looks like it is a pivot table)?
Or can you access the RAW data so you can build logic in that to lookup IF class 1-5 has been completed and if so what date each? I prefer to use a few easy steps that are easier to trace for when I hand a project off to someone else.

Can you post a sample of the raw data?

[allynj]

I agree with you but I am a PT temp worker and I don't want to push it. I need this gig
I'm using a pivot table to generate the data, then I cut and pasted it so it is no longer a pivot table.

Here is a greatly simplified, greatly shortened, version of a made up spreadsheet. The actual datasheet is 1400 rows made up of 48 or so Excel tables I have imported from cvent (there being 48 dates so far of the five classes....)

[newdoverman]

Change the range to a table. That way the "funder" can see the data in many different ways just by clicking on the Headers and choosing what the "funder" wants.

The parameters of what the "funder" wants are too vague. Can you define them better?

[allynj]

The way my pivot table was running, I had a column for every date a course ran, which ran to many columns. The pivot was "counting" a 1 for whenever the person took one of the 5 required classes. At some point they are supposed to fulfill all 5 classes. Most people have only taken 1, 2 or 3.

My boss and the funder client want it narrowed down to 5 columns....for the title of the class, and the actual date that the person took the class underneath.

So, for example:
Name Title Organization Work City Work County Class 1 Class 2 Class 3 Class 4 Class 5

[mikeTRON]

Haha, part time means you have to wow them with the pivot tables

Seriously though, you can still use a pivot table to do your aggregation in a step between the raw data and the dashboard that is displayed in the column headings you have listed above.


As for the sample data you posted, where is your logic for class 1-5? I would develop logic to the right in a 5 simple columns that state where class 1-5 has been completed but I cant follow the sample.

Does that make sense?

[allynj]

Hi,
The top version is a copy of a pivot table where there is a concatenated field of Class:Date in the Column field returning a value of 1 if the person took a class on that date (so right now there are multiple dates and mutiple classes)

My boss and the client want it shortened to read only the 5 Class titles, and to have it say the actual date the person took the class (could be 1 of many dates).

So, somehow, I know I need something to say, "if 1 on this date, report actual date in this column for Class A, blank if nothing" and so on for Class 2, Class 3, etc.....
I don't know code or macros. Do I run a pivot table off another pivot table, or a slicer, or an vlookup?

Sigh, off to Google....

[mikeTRON]

What I am saying is I would want to see the SOURCE data for your pivot table. WITH THAT you can add 5 columns to the left that has an if statement for each class. THEN you can EASILY pull all this together. VBA shouldnt be needed.

Does that make sense? This is why I wanted to see the source data for your pivot table so I could SHOW you isntead of wasting out time explaining the possibilities

[allynj]

Sorry for being a dunce - let me go scrub something that can me uploaded!!!
Thank you!

[mikeTRON]

Haha, no worries.

Im trying to help you help me help you

[allynj]

Alrighty then, here is what the data sorta looks like (it's been changed)....
thoughts???

[allynj]

Do you want to see the pivot table I used to get the original result also?

[mikeTRON]

How often will you have to update this?
How often will new people come into the list?

I would still walk them through the pivot table solution since it gives them the EXACT data they want without ANY work, then also slicers so they can view data exactly as they wany. You can be very pragmatic about it, but let them know how EASY it will be for THEM to manipulate the data nd view it in the way THEY want. I included a few random splicers just for the heck of it.

I also showed you two other ways you could go about soliving the problem without pivot tables or with a pivot as an intermediate step.

Class Dates solutions.xlsx

[allynj]

OK, I'm digesting this!
From what I can gather, this would be updated about every 6-8 weeks with about 50 people added to the list.
The "Summary Pivot" Sheet that includes the Timeline that says it works in Excel 2013 or higher - we only have 2010 here in the office....but it looks like it is working.

[mikeTRON]

Oh well if it is updated that infrequently then it wouldn't be that time consuming to manually add the names and such.

Sorry, I forget which features come from 2010 or 2013 as I have only been using 13 lately. It is cool that it works, but I would remove it to prevent issues (unless you want to use it to campaign FOR buying 2013 ). You can just make a slicer with the DATA dimension instead of the timeline but it will work.

In all seriousness I would show BOTH solutions and show them the benefits of the pivot table and being able to filter and sort very easily. Tell them you could even offer support to whoever the tool will be going off to (get more facetime with more people which I would imagine is an added bonus when trying to land a full time gig). This also shows that you are capable of doing neat things with excel haha