Pivot Table to show only items not appearing in the last month

[prosperosbks]

There may be a better way of doing this than a pivot table. But here is my dilemma.

I have a number of wigets (lets say they are books)
People borrow the books and I record that on a sheet.
Book1 | dateout| dateback| person who borrowed the book.
Book2 | etc.. etc...

I create my pivot table but what I really want to know is the books that didnt get borrowed last month. or for a period of 30 days or so (i would like to custom that maybe 45 days or 60 days)

So I would be left with a report that would show me the books that are not borrowed last month, but then if a book does get borrowed it wouldn't appear the next time I refreshed the pivot table.

Thanks in advance for your assistance.

 

[James006]

Hi,

Guess you can easily adapt the following to your situation :

Excel Pivot Table Tutorial -- Grouping Data

HTH

 

[prosperosbks]

Hi,

Guess you can easily adapt the following to your situation :

Excel Pivot Table Tutorial -- Grouping Data



HTH

The would certainly group my data into time buckets that I specify but the problem is that I cannot see a way to apply a "show me this book if it DOES NOT appear in this group of last month.?

 

[James006]

Hi,

The easiest way out is to add a specific column to your database, a field with the adequate formula you need to result in a True/False indicator you can then incorporate in your pivot table ...

HTH

 

[par60056]

I don't really think you need a pivot table. Unless you are trying to show multiple periods (1 month, 3 month, 1 year) at the same time.

Pivot tables are mostly for summarizing.

I would think about adding appropriate columns "=Bx>(now()-30)" (Assuming dateOut is in B) and them use autofilter on the time period columns

 

[prosperosbks]

I don't really think you need a pivot table. Unless you are trying to show multiple periods (1 month, 3 month, 1 year) at the same time.

Pivot tables are mostly for summarizing.

I would think about adding appropriate columns "=Bx>(now()-30)" (Assuming dateOut is in B) and them use autofilter on the time period columns

Good idea.
I love pivot tables so probably use them when i dont really need to.
I used the formula and generated a true and false statement then used that in the pivot by putting the books in the columns and the true/false out in the last 30days in the row and a count of the true/false then used some conditional formating to highlight the less than 1. whilst the table is wide, 53 books, it is effective and easily read which is perfect.

Thanks for your help.

 

[par60056]

personally I would switch the rows and columns. I would expect the days out can be narrower columns and you can make the book column wide enough to read. But it depends on what it really looks like.