How to Structure an Excel 2010 Pivot Table to Display Docs Sorted by Doc# Under Columns of Due Dates

Using:  Excel 2010

I will have a list of hundreds of documents (with document numbers ranging from 1 to say 1000 but not always sequential).  In a nearby column, there will be a Due Date (which may be updated sometimes based on input from the author).  This will be my source table

I'm trying to create a Pivot Table which creates a table with all of the Due Dates as Column Headings and would list under each Due Date, sorted numerically, the Docs that are due that day.  At the bottom of each row would be the total number of docs due that day.

The attached picture shows what I'm trying to do.
Pivot-Table-Doc-by-Due-Date.jpg
qengAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wilder1626Commented:
Hi qeng

Is that what you are looking for?
Pivot-tble.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Commented:
Another way could be
PivotPivot-tble.xlsx
0
qengAuthor Commented:
Hey Jean-Marc,

Very close but I'm trying to have the doc numbers appear under the dates, without blank rows because the overall number of documents will be many hundreds and on one day, maybe documents 11, 319 and 522 are due, and on another day maybe documents 5, 31, and 499 are due, etc.

So it would be a lot easier (otherwise people will have to look across many pages to find what's due) if the Pivot Table created a list like this:

Date1   Date 2  etc.
11               5
319           31
522         499

Maybe I will have to use the Pivot Table to extract the date, then some VBA to construct the table?  I was thinking there was a way to create this using just the Pivot Table.

It would be easy enough to sort the first date to get say 11, 319 and 522 to appear at the top but that will much up the adjacent days (since all rows would sort, and not just the cells in individual columns)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

qengAuthor Commented:
Sorry I was posting when your second example came it.  So my comment above applied to your first example.

The second example correctly orders the docs.

So I think I could easily enough create independent columns (1 column tables) on another spreadsheet, say with a blank column of width 1 between each Date column. So i could then filter each Date for sorted, non-blank cells.
0
qengAuthor Commented:
I'll be back in a few hrs (dinner time).
0
qengAuthor Commented:
Hi Jean-Marc,

My apologies for the long delay.  Swamped, swamped, swamped.

In rechecking, the problem with the first pivot table approach you were suggesting is that if one author has more than one document due on a given day, the pivot table in your first example only shows the highest number document.

For example, author:  'John' has two docs due on 2015-03-15, doc #1 and doc #2 but the pivot table only shows that he has doc #2.

Pivot-Table-1-Doesnt-Show-More-Than-One-
I'll send this comment now before I lose it and provide feedback on the second pivot table example you provided.
0
qengAuthor Commented:
Jean-Marc:  Further comment about the first Pivot Table solution your proposed:

On doing a bit more investigation I find that instead of listing the doc numbers, by Author, by Due Date, it instead multiplies the doc#s (see attached image) ... cool ... but it wouldn't make sense in my application :)

First pivot table solution multiplies the doc #s instead of listing them by author, by due date
I'll tend my attention now to your second pivot table example.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.