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.
 

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
236
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

hafe4k

New Member
Joined
Jan 7, 2019
Messages
26
Can I add to the same cell that is doing the filter?
=FILTER(A2:B45,B2:B45<TODAY()+93,"") add it there somehow?
 

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
236
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
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.
 

hafe4k

New Member
Joined
Jan 7, 2019
Messages
26
Thanks so much both - much appreciated. Mixture of the both does the trick! Much appreciated.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,136,956
Messages
5,678,766
Members
419,783
Latest member
yazan

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
Top