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
[TABLE="class: grid, width: 388"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]expire
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]empl1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]empl2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]empl3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11/1/2018
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]empl4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3/7/2018
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]empl5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]empl6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]empl7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]empl8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]empl9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]empl10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]empl11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]empl12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7/1/2018
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]empl13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]empl14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]empl15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]empl16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]empl17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2018
[/TD]
[/TR]
</tbody>[/TABLE]

Summary Sheet
[TABLE="class: grid, width: 132"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]name
[/TD]
[TD]exp date
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]empl1
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]empl2
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]empl4
[/TD]
[TD="align: right"]3/7/2018
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]empl5
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]empl6
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]empl7
[/TD]
[TD="align: right"]11/7/2017
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]empl8
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]empl9
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]empl10
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]empl11
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]empl13
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]empl14
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]empl15
[/TD]
[TD="align: right"]12/1/2017
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]empl16
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
</tbody>[/TABLE]

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.