Array Query on Filter()

[mart_mrexcel]

I have an auto-filter for Anniversary & Birthday in a Month(Today()). In the table,
a.) how can I filter the Hire date and/or birth date in column "G"
b.) In column H, I want to put "Anniversary" or "Birthday"
c.) in the given example, Jose has 2 events in January (i.e. Anniversary and Birthday), I want to make his name appear 2 times in the table with Anniversary and Birthday in Column H.

Thank you

Book1
	A	B	C	D	E	F	G	H	I
1	Employee ID	Hire Date	Birth Date			Emp	Date	Header Name
2	Stacy	16-Jan-07	01-Jun-68			Stacy
3	Derek	10-Jul-16	14-Jul-75			Jose
4	Martin	21-Sep-17	16-Aug-82			Peter
5	Jose	18-Jan-18	13-Jan-95
6	Peter	26-Nov-17	13-Jan-94
7	Lance	25-Feb-18	01-Feb-95
8	Judith	01-Apr-18	15-Mar-82
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=FILTER(EmpMstr[Employee ID],(MONTH(EmpMstr[Hire Date])=MONTH(TODAY()))+(MONTH(EmpMstr[Birth Date])=MONTH(TODAY())))
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
2	Employee ID	Hire Date	Birth Date			Emp	Date	Header Name
3	Stacy	16/01/2007	01/06/1968			Stacy	16/01/2007	Hire Date
4	Derek	10/07/2016	14/07/1975			Jose	18/01/2018	Hire Date
5	Martin	21/09/2017	16/08/1982			Jose	13/01/1995	Birth Date
6	Jose	18/01/2018	13/01/1995			Peter	13/01/1994	Birth Date
7	Peter	26/11/2017	13/01/1994
8	Lance	25/02/2018	01/02/1995
9	Judith	01/04/2018	15/03/1982
10
Lists

Cell Formulas
Range	Formula
F3:H6	F3	=LET(d,EmpMstr[[Hire Date]:[Birth Date]],m,MONTH(TODAY()),HSTACK(TOCOL(IF((MONTH(d)=m),EmpMstr[Employee ID],x),2),TOCOL(IF((MONTH(d)=m),d,x),2),TOCOL(IF((MONTH(d)=m),EmpMstr[[#Headers],[Hire Date]:[Birth Date]],x),2)))
Dynamic array formulas.

 

[mart_mrexcel]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
2	Employee ID	Hire Date	Birth Date			Emp	Date	Header Name
3	Stacy	16/01/2007	01/06/1968			Stacy	16/01/2007	Hire Date
4	Derek	10/07/2016	14/07/1975			Jose	18/01/2018	Hire Date
5	Martin	21/09/2017	16/08/1982			Jose	13/01/1995	Birth Date
6	Jose	18/01/2018	13/01/1995			Peter	13/01/1994	Birth Date
7	Peter	26/11/2017	13/01/1994
8	Lance	25/02/2018	01/02/1995
9	Judith	01/04/2018	15/03/1982
10
Lists

Cell Formulas
Range	Formula
F3:H6	F3	=LET(d,EmpMstr[[Hire Date]:[Birth Date]],m,MONTH(TODAY()),HSTACK(TOCOL(IF((MONTH(d)=m),EmpMstr[Employee ID],x),2),TOCOL(IF((MONTH(d)=m),d,x),2),TOCOL(IF((MONTH(d)=m),EmpMstr[[#Headers],[Hire Date]:[Birth Date]],x),2)))
Dynamic array formulas.

Thanks Fluff, im receiving an #NAME? error though. It seems that TOCOL() and HSTACK() is not yet updated in my 365. I wonder if there is other formula approach to solve it. Thank you

 

[alansidman]

An alternative is to use Power Query and build a parameter query that you will look up the particular month.
1. Load your data table to the PQ editor
2. Use the following Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hire Date", type date}, {"Birth Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee ID"}, "Attribute", "Value"),
    #"Inserted Month Name" = Table.AddColumn(#"Unpivoted Other Columns", "Month Name", each Date.MonthName([Value]), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Month Name", each ([Month Name] = Table3)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Month Name"})
in
    #"Removed Columns"

Create a new table in Excel that looks like the following with the second line a validation input of the month name

Month
January

Then apply the following Mcode to this new table

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Month = Source{0}[Month]
in
    Month

Once both codes are in place, then close and load your first query to an Excel table. The result should look like the following

Employee ID	Attribute	Value
Stacy	Hire Date	1/16/2007
Jose	Hire Date	1/18/2018
Jose	Birth Date	1/13/1995
Peter	Birth Date	1/13/1994

 

[mart_mrexcel]

An alternative is to use Power Query and build a parameter query that you will look up the particular month.
1. Load your data table to the PQ editor
2. Use the following Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hire Date", type date}, {"Birth Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee ID"}, "Attribute", "Value"),
    #"Inserted Month Name" = Table.AddColumn(#"Unpivoted Other Columns", "Month Name", each Date.MonthName([Value]), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Month Name", each ([Month Name] = Table3)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Month Name"})
in
    #"Removed Columns"

Create a new table in Excel that looks like the following with the second line a validation input of the month name

Month
January

Then apply the following Mcode to this new table

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Month = Source{0}[Month]
in
    Month

Once both codes are in place, then close and load your first query to an Excel table. The result should look like the following

Employee ID	Attribute	Value
Stacy	Hire Date	1/16/2007
Jose	Hire Date	1/18/2018
Jose	Birth Date	1/13/1995
Peter	Birth Date	1/13/1994

Thank you alansidman

 

[Fluff]

Thanks Fluff, im receiving an #NAME?

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	Employee ID	Hire Date	Birth Date			Emp	Date	Header Name
3	Stacy	16/01/2007	01/06/1968			Stacy	16/01/2007	Hire Date
4	Derek	10/07/2016	14/07/1975			Jose	18/01/2018	Hire Date
5	Martin	21/09/2017	16/08/1982			Jose	13/01/1995	Birth Date
6	Jose	18/01/2018	13/01/1995			Peter	13/01/1994	Birth Date
7	Peter	26/11/2017	13/01/1994
8	Lance	25/02/2018	01/02/1995
9	Judith	01/04/2018	15/03/1982
Lists

Cell Formulas
Range	Formula
F3:H6	F3	=LET(d,EmpMstr[[Hire Date]:[Birth Date]],m,MONTH(TODAY()),c,COLUMNS(d),s,SEQUENCE(c*ROWS(d),,0),xa,INDEX(EmpMstr[Employee ID],INT(s/c)+1),xb,INDEX(d,INT(s/c)+1,MOD(s,c)+1),xc,INDEX(EmpMstr[[#Headers],[Hire Date]:[Birth Date]],MOD(s,c)+1),FILTER(CHOOSE({1,2,3},xa,xb,xc),MONTH(xb)=m))
Dynamic array formulas.

 

[mart_mrexcel]

Thank you Mr. Fluff

 

[Fluff]

You're welcome & thanks for the feedback.