Rolling 12 months expiry dates view

hafe4k

New Member
Joined
Jan 7, 2019
Messages
26
Hi there,

I have a list of rows (books) around 1500 records - each book has a date they need to be checked and audited to ensure the conditions of the book is up to scratch. Some books need this reviewing every 3 months, 6 months , some 12 months and some 24 months. The key field is 'review by' date - this date is in the future and is manually updated by somebody.

Currently its a really manual task in checking through the list using filters.

I would like to create an easy to see report or some sort which will easily show me a list of:

In the next 3 months these books are due:
In the next 6 months these:
In the next 12 months these books:
and these are books that have over 12 months left. see an easy way

What would be the best approach please

Thanks in advance.
 
The Unique function:
MrExcelPlayground2.xlsx
ABCDEF
1BookTypeReview By Date
2Book 1Hard5/1/2021Book 1Hard
3Book 1Hard5/14/2021Book 1Soft
4Book 1Soft5/16/2021Book 4Hard
5Book 4Hard6/9/2021Book 4Soft
6Book 4Soft6/22/2021Book 4eBook
7Book 4eBook7/20/2021Book 7Hard
8Book 7Hard8/13/2021Book 9Hard
9Book 7Hard9/4/2021Book 10Soft
10Book 9Hard10/3/2021Book 10eBook
11Book 10Soft10/28/2021
12Book 10Soft11/12/2021
13Book 10eBook11/27/2021
Sheet13
Cell Formulas
RangeFormula
E2:F10E2=UNIQUE(A2:B13)
Dynamic array formulas.
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Can I add to the same cell that is doing the filter?
=FILTER(A2:B45,B2:B45<TODAY()+93,"") add it there somehow?
 
Upvote 0
If the date of a title and style are always the same, then this will work (note the part in the bottom right):

MrExcelPlayground2.xlsx
ABCDEFGHIJK
1BookTypeReview By Date3 months
2Book 1Hard5/1/2021Book 1Hard5/1/2021BookReview By Date
3Book 1Hard5/1/2021Book 1Soft5/16/2021Book 1Hard5/1/2021
4Book 1Soft5/16/2021Book 4Hard6/9/2021Book 1Soft5/16/2021
5Book 4Hard6/9/2021Book 4Soft6/22/2021Book 4Hard6/9/2021
6Book 4Soft6/22/2021Book 4eBook7/20/2021Book 4Soft6/22/2021
7Book 4eBook7/20/2021Book 7Hard9/4/2021Book 4eBook7/20/2021
8Book 7Hard8/13/2021Book 9Hard10/3/2021
9Book 7Hard9/4/2021Book 10Soft11/12/2021
10Book 9Hard10/3/2021Book 10eBook11/27/2021One Formula
11Book 10Soft10/28/2021Book 1Hard5/1/2021
12Book 10Soft11/12/2021Book 1Soft5/16/2021
13Book 10eBook11/27/2021Book 4Hard6/9/2021
14Book 1311/30/2021Book 4Soft6/22/2021
15Book 1412/21/2021Book 4eBook7/20/2021
Sheet13
Cell Formulas
RangeFormula
E2:F10E2=UNIQUE(A2:B13)
I3:K7I3=FILTER(E2:G10,G2:G10<TODAY()+93,"")
G2:G10G2=MAXIFS($C$2:$C$13,$A$2:$A$13,E2,$B$2:$B$13,F2)
I11:K15I11=FILTER(UNIQUE(A2:C13),INDEX(UNIQUE(A2:C13),,3)<TODAY()+93,"")
Dynamic array formulas.


So if the date assoiciated with the two rows of Book1 and Hardcover is the same, it will work. If they are different dates for the two records of title and format, then they will be different outputs.
 
Upvote 0
Solution
Another option
+Fluff 1.xlsm
ABCDEFGHIJK
1BookTypeReview By Date3 months12 months
2Book 1Hard01/05/2021BookReview By DateBookReview By Date
3Book 1Hard01/05/2021Book 1Hard01/05/2021Book 1Hard01/05/2021
4Book 1Soft16/05/2021Book 1Soft16/05/2021Book 1Soft16/05/2021
5Book 4Hard09/06/2021Book 4Hard09/06/2021Book 4Hard09/06/2021
6Book 4Soft22/06/2021Book 4Soft22/06/2021Book 4Soft22/06/2021
7Book 4eBook20/07/2021Book 4eBook20/07/2021Book 4eBook20/07/2021
8Book 7Hard13/08/2021Book 7Hard13/08/2021
9Book 7Hard04/09/2021Book 7Hard04/09/2021
10Book 9Hard03/10/2021Book 9Hard03/10/2021
11Book 10Soft28/10/2021Book 10Soft28/10/2021
12Book 10Soft12/11/2021Book 10Soft12/11/2021
13Book 10eBook27/11/2021Book 10eBook27/11/2021
14Book 13Hard30/11/2021Book 13Hard30/11/2021
15Book 14Soft21/12/2021Book 14Soft21/12/2021
16
Master
Cell Formulas
RangeFormula
E3:G7E3=UNIQUE(FILTER(A2:C15,C2:C15<TODAY()+93,""))
I3:K15I3=UNIQUE(FILTER(A2:C15,C2:C15<TODAY()+366,""))
Dynamic array formulas.
 
Upvote 0
Thanks so much both - much appreciated. Mixture of the both does the trick! Much appreciated.
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top