qeng
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wilder1626
Another way could be
Pivot-tble.xlsx
Pivot-tble.xlsx
ASKER
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)
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)
ASKER
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.
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.
ASKER
I'll be back in a few hrs (dinner time).
ASKER
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.
I'll send this comment now before I lose it and provide feedback on the second pivot table example you provided.
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.
I'll send this comment now before I lose it and provide feedback on the second pivot table example you provided.
ASKER
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 :)
I'll tend my attention now to your second pivot table example.
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 :)
I'll tend my attention now to your second pivot table example.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.