• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

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
0
qeng
Asked:
qeng
  • 5
  • 2
1 Solution
 
Wilder1626Commented:
Hi qeng

Is that what you are looking for?
Pivot-tble.xlsx
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now