Display List of Licenses Due

[paulpax84]

Hi everyone first of all my apologies if this is something really simple I should be able to figure out on my own.

I currently have a spread sheet in which users input the date an individuals license is due to expire. For example in the worksheet titled "MHE" the employees name would be in cell A8 with the date the license is due to expire sitting in cell F8. The data table can run approximately 200 rows down with each individual employee having a full row detailing the expiration dates for various pieces of kit.

I would like to formulate a list on a separate sheet title "Summary" showing any licenses which would be due to expire in the next six months. I know I could use IF functions to return the name if the date falls within a range for each row. Although, for example if only rows 30, 60 and 80 were due to expire it would mean the list on the summary tab would have lots of empty cells with data sporadically displayed and without sorting would remain this way.

Is there a way to populate this list without have these huge gaps appearing?

Hopefully I have explained it in an understandable way.

Any help would be much appreciated.

 

[Scott T]

Sheet MHE

	A	B	C	D	E	F
7	name					expire
8	empl1					10/1/2017
9	empl2					10/1/2017
10	empl3					11/1/2018
11	empl4					3/7/2018
12	empl5					10/1/2017
13	empl6					10/1/2017
14	empl7					10/1/2017
15	empl8					10/1/2017
16	empl9					10/1/2017
17	empl10					10/1/2017
18	empl11					10/1/2017
19	empl12					7/1/2018
20	empl13					10/1/2017
21	empl14					10/1/2017
22	empl15					10/1/2017
23	empl16					10/1/2017
34	empl17					10/1/2018

<tbody>
</tbody>

Summary Sheet

	A	B
1	name	exp date
2	empl1	10/1/2017
3	empl2	10/1/2017
4	empl4	3/7/2018
5	empl5	10/1/2017
6	empl6	10/1/2017
7	empl7	11/7/2017
8	empl8	10/1/2017
9	empl9	10/1/2017
10	empl10	10/1/2017
11	empl11	10/1/2017
12	empl13	10/1/2017
13	empl14	10/1/2017
14	empl15	12/1/2017
15	empl16	10/1/2017

<tbody>
</tbody>

On the Summary sheet in A2
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IF(ROWS($A$2:A2)>COUNTIFS(MHE!$F$8:$F$24,"<"&EDATE(TODAY(),6)),"",INDEX(MHE!$A$8:$A$24,SMALL(IF(EDATE(TODAY(),6)>MHE!$F$8:$F$24,ROW(MHE!F$8:F$24)-ROW(MHE!F$8)+1),ROWS($A$2:A2))))

in B2
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IF(ROWS($A$2:B2)>COUNTIFS(MHE!$F$8:$F$24,"<"&EDATE(TODAY(),6)),"",INDEX(MHE!$F$8:$F$24,SMALL(IF(EDATE(TODAY(),6)>MHE!$F$8:$F$24,ROW(MHE!G$8:G$24)-ROW(MHE!G$8)+1),ROWS($A$2:B2))))

Copy the formulas down

 

[AlanY]

this solution also arranged them with the earliest expired at the top

Excel 2013/2016
	A	B	C	D	E	F
1	Name	Expire date			Name	Expire date
2	Name1	03/05/2018			Name8	18/03/2018
3	Name2	20/06/2018			Name20	23/04/2018
4	Name3	23/01/2018			Name1	03/05/2018
5	Name4	01/11/2017			Name18	16/05/2018
6	Name5	27/06/2018			Name2	20/06/2018
7	Name6	10/01/2018			Name5	27/06/2018
8	Name7	31/07/2018			Name13	14/07/2018
9	Name8	18/03/2018			Name11	29/07/2018
10	Name9	12/12/2017			Name7	31/07/2018
11	Name10	24/08/2018			Name12	04/08/2018
12	Name11	29/07/2018			Name10	24/08/2018
13	Name12	04/08/2018
14	Name13	14/07/2018
15	Name14	11/02/2018
16	Name15	21/11/2017
17	Name16	17/01/2018
18	Name17	10/12/2017
19	Name18	16/05/2018
20	Name19	04/01/2018
21	Name20	23/04/2018
Sheet1

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX($B$2:$B$21,MATCH(E2,$A$2:$A$21,0)),"")
E2	{=IFERROR(INDEX($A$2:$A$21,MATCH(SMALL(IF(($B$2:$B$21-TODAY())>365/2,$B$2:$B$21),ROW(2:2)-ROW($1:$1)),$B$2:$B$21,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.