Go from Events with list of dates to Dates with list of events

[abhinavk11]

Hello!

I have what seems to be a simple task, but frustratingly I can't find out a way to automate it.

I have a table of universities and interview dates. Each university has multiple interview dates. Each row is a university name (Column 1) and the dates it's having interviews (in subsequent columns).

I'm trying to summarise by interview date. I'd like to make a summary table where each row is a date (Column 1) and the universities who have interviews on that date (in subsequent Columns).

I've tried pivot tables, but they're not able to handle having multiple dates per university.

Could anyone please help? Range pasted at bottom.

Thank you

--Abhinav

Program Name	Date1	Date2	Date3	Date4	Date5	Date6	Date7
U Texas/ Methodist -Galveston	27-Oct-20	9-Nov-20	20-Nov-20	8-Dec-20
Illinois Eye and Ear Infirmary	29-Oct-20	30-Oct-20	2-Nov-20
U Kentucky	30-Oct-20	6-Nov-20	20-Nov-20
University of Mississippi	30-Oct-20	31-Oct-20	7-Nov-20	21-Nov-20
UT Southwestern- Dallas	30-Oct-20	6-Nov-20	4-Dec-20	11-Dec-20	8-Jan-21
Boston University	2-Nov-20	9-Dec-20	30-Nov-20
Scheie Eye Inst / U Penn	2-Nov-20	11-Nov-20	20-Nov-20
U Utah	4-Nov-20	6-Nov-20	9-Nov-20	11-Nov-20
Wake Forest U	4-Nov-20	6-Nov-20	18-Nov-20	20-Nov-20	2-Dec-20	4-Dec-20	16-Dec-20
Wilmer-Johns Hopkins	5-Nov-20	19-Nov-20
U Alabama - Birmingham	6-Nov-20
U Oklahoma-Oklahoma City	6-Nov-20	9-Nov-20	23-Nov-20	11-Dec-20
Case Western Reserve U	8-Nov-20	20-Nov-20	21-Nov-20
U Pittsburgh	9-Nov-20	10-Nov-20
Stroger/Cook Cty-Chicago	10-Nov-20	12-Nov-20
U Texas - Houston	16-Nov-20	20-Nov-20	30-Nov-20	4-Dec-20
U Minnesota - Mpls.	19-Nov-20	20-Nov-20
U Washington	19-Nov-20	3-Dec-20	4-Dec-20
Duke University	20-Nov-20	4-Dec-20
U California - Davis	20-Nov-20	4-Dec-20	11-Dec-20
U Tennessee - Memphis	20-Nov-20	11-Dec-20
Oregon HSU	21-Nov-20	5-Dec-20
USC Roski Eye Institute – Residency	1-Dec-20	2-Dec-20
Stanford Ophthalmology Advanced Research Residency Program (SOAR)	2-Dec-20	8-Dec-20	14-Dec-20
Med U South Carolina	3-Dec-20	4-Dec-20
U Arkansas	3-Dec-20	4-Dec-20	5-Dec-20	10-Dec-20	11-Dec-20	12-Dec-20
University of South Florida	3-Dec-20	4-Dec-20
U Colorado	4-Dec-20	6-Dec-20	11-Dec-20
Emory University	5-Dec-20	6-Dec-20
U Louisville	5-Dec-20	6-Dec-20	12-Dec-20
Cleveland Clinic - Cole Eye Institute	6-Dec-20	7-Dec-20	8-Dec-20
Sinai Hospital -Baltimore	7-Dec-20	9-Dec-20	15-Dec-20	17-Dec-20
U California - San Diego	7-Dec-20	14-Dec-20
U Miami / Bascom Palmer	9-Dec-20	10-Dec-20	11-Dec-20
Wills Eye Residency Program at Jefferson	14-Dec-20	15-Dec-20
U California - LA	16-Dec-20	6-Jan-21	13-Jan-21
Baylor College of Medicine/Cullen Eye Institute	4-Jan-21	5-Jan-21	8-Jan-21

 

[alansidman]

with Power Query which is called Get and Transform in your version of Excel and found on the Data Tab of the Ribbon. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Program Name", type text}, {"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}, {"Date5", type date}, {"Date6", type date}, {"Date7", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Program Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Value"}, {{"Data", each _, type table [Program Name=nullable text, Attribute=text, Value=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Program Name", "Index"}, {"Custom.Program Name", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Program Name")
in
    #"Pivoted Column"

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Value	1	2	3	4	5	6	7	8	9	10
2	10/27/2020	U Texas/ Methodist -Galveston
3	10/29/2020	Illinois Eye and Ear Infirmary
4	10/30/2020	Illinois Eye and Ear Infirmary	U Kentucky	University of Mississippi	UT Southwestern- Dallas
5	10/31/2020	University of Mississippi
6	11/2/2020	Illinois Eye and Ear Infirmary	Boston University	Scheie Eye Inst / U Penn
7	11/4/2020	U Utah	Wake Forest U
8	11/5/2020	Wilmer-Johns Hopkins
9	11/6/2020	U Kentucky	UT Southwestern- Dallas	U Utah	Wake Forest U	U Alabama - Birmingham	U Oklahoma-Oklahoma City
10	11/7/2020	University of Mississippi
11	11/8/2020	Case Western Reserve U
12	11/9/2020	U Texas/ Methodist -Galveston	U Utah	U Oklahoma-Oklahoma City	U Pittsburgh
13	11/10/2020	U Pittsburgh	Stroger/Cook Cty-Chicago
14	11/11/2020	Scheie Eye Inst / U Penn	U Utah
15	11/12/2020	Stroger/Cook Cty-Chicago
16	11/16/2020	U Texas - Houston
17	11/18/2020	Wake Forest U
18	11/19/2020	Wilmer-Johns Hopkins	U Minnesota - Mpls.	U Washington
19	11/20/2020	U Texas/ Methodist -Galveston	U Kentucky	Scheie Eye Inst / U Penn	Wake Forest U	Case Western Reserve U	U Texas - Houston	U Minnesota - Mpls.	Duke University	U California - Davis	U Tennessee - Memphis
20	11/21/2020	University of Mississippi	Case Western Reserve U	Oregon HSU
21	11/23/2020	U Oklahoma-Oklahoma City
22	11/30/2020	Boston University	U Texas - Houston
23	12/1/2020	USC Roski Eye Institute – Residency
24	12/2/2020	Wake Forest U	USC Roski Eye Institute – Residency	Stanford Ophthalmology Advanced Research Residency Program (SOAR)
25	12/3/2020	U Washington	Med U South Carolina	U Arkansas	University of South Florida
26	12/4/2020	UT Southwestern- Dallas	Wake Forest U	U Texas - Houston	U Washington	Duke University	U California - Davis	Med U South Carolina	U Arkansas	University of South Florida	U Colorado
27	12/5/2020	Oregon HSU	U Arkansas	Emory University	U Louisville
28	12/6/2020	U Colorado	Emory University	U Louisville	Cleveland Clinic - Cole Eye Institute
29	12/7/2020	Cleveland Clinic - Cole Eye Institute	Sinai Hospital -Baltimore	U California - San Diego
30	12/8/2020	U Texas/ Methodist -Galveston	Stanford Ophthalmology Advanced Research Residency Program (SOAR)	Cleveland Clinic - Cole Eye Institute
31	12/9/2020	Boston University	Sinai Hospital -Baltimore	U Miami / Bascom Palmer
32	12/10/2020	U Arkansas	U Miami / Bascom Palmer
33	12/11/2020	UT Southwestern- Dallas	U Oklahoma-Oklahoma City	U California - Davis	U Tennessee - Memphis	U Arkansas	U Colorado	U Miami / Bascom Palmer
34	12/12/2020	U Arkansas	U Louisville
35	12/14/2020	Stanford Ophthalmology Advanced Research Residency Program (SOAR)	U California - San Diego	Wills Eye Residency Program at Jefferson
36	12/15/2020	Sinai Hospital -Baltimore	Wills Eye Residency Program at Jefferson
37	12/16/2020	Wake Forest U	U California - LA
38	12/17/2020	Sinai Hospital -Baltimore
39	1/4/2021	Baylor College of Medicine/Cullen Eye Institute
40	1/5/2021	Baylor College of Medicine/Cullen Eye Institute
41	1/6/2021	U California - LA
42	1/8/2021	UT Southwestern- Dallas	Baylor College of Medicine/Cullen Eye Institute
Sheet2

 

[abhinavk11]

with Power Query which is called Get and Transform in your version of Excel and found on the Data Tab of the Ribbon. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Program Name", type text}, {"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}, {"Date5", type date}, {"Date6", type date}, {"Date7", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Program Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Value"}, {{"Data", each _, type table [Program Name=nullable text, Attribute=text, Value=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Program Name", "Index"}, {"Custom.Program Name", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Program Name")
in
    #"Pivoted Column"

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Value	1	2	3	4	5	6	7	8	9	10
2	10/27/2020	U Texas/ Methodist -Galveston
3	10/29/2020	Illinois Eye and Ear Infirmary
4	10/30/2020	Illinois Eye and Ear Infirmary	U Kentucky	University of Mississippi	UT Southwestern- Dallas
5	10/31/2020	University of Mississippi
6	11/2/2020	Illinois Eye and Ear Infirmary	Boston University	Scheie Eye Inst / U Penn
7	11/4/2020	U Utah	Wake Forest U
8	11/5/2020	Wilmer-Johns Hopkins
9	11/6/2020	U Kentucky	UT Southwestern- Dallas	U Utah	Wake Forest U	U Alabama - Birmingham	U Oklahoma-Oklahoma City
10	11/7/2020	University of Mississippi
11	11/8/2020	Case Western Reserve U
12	11/9/2020	U Texas/ Methodist -Galveston	U Utah	U Oklahoma-Oklahoma City	U Pittsburgh
13	11/10/2020	U Pittsburgh	Stroger/Cook Cty-Chicago
14	11/11/2020	Scheie Eye Inst / U Penn	U Utah
15	11/12/2020	Stroger/Cook Cty-Chicago
16	11/16/2020	U Texas - Houston
17	11/18/2020	Wake Forest U
18	11/19/2020	Wilmer-Johns Hopkins	U Minnesota - Mpls.	U Washington
19	11/20/2020	U Texas/ Methodist -Galveston	U Kentucky	Scheie Eye Inst / U Penn	Wake Forest U	Case Western Reserve U	U Texas - Houston	U Minnesota - Mpls.	Duke University	U California - Davis	U Tennessee - Memphis
20	11/21/2020	University of Mississippi	Case Western Reserve U	Oregon HSU
21	11/23/2020	U Oklahoma-Oklahoma City
22	11/30/2020	Boston University	U Texas - Houston
23	12/1/2020	USC Roski Eye Institute – Residency
24	12/2/2020	Wake Forest U	USC Roski Eye Institute – Residency	Stanford Ophthalmology Advanced Research Residency Program (SOAR)
25	12/3/2020	U Washington	Med U South Carolina	U Arkansas	University of South Florida
26	12/4/2020	UT Southwestern- Dallas	Wake Forest U	U Texas - Houston	U Washington	Duke University	U California - Davis	Med U South Carolina	U Arkansas	University of South Florida	U Colorado
27	12/5/2020	Oregon HSU	U Arkansas	Emory University	U Louisville
28	12/6/2020	U Colorado	Emory University	U Louisville	Cleveland Clinic - Cole Eye Institute
29	12/7/2020	Cleveland Clinic - Cole Eye Institute	Sinai Hospital -Baltimore	U California - San Diego
30	12/8/2020	U Texas/ Methodist -Galveston	Stanford Ophthalmology Advanced Research Residency Program (SOAR)	Cleveland Clinic - Cole Eye Institute
31	12/9/2020	Boston University	Sinai Hospital -Baltimore	U Miami / Bascom Palmer
32	12/10/2020	U Arkansas	U Miami / Bascom Palmer
33	12/11/2020	UT Southwestern- Dallas	U Oklahoma-Oklahoma City	U California - Davis	U Tennessee - Memphis	U Arkansas	U Colorado	U Miami / Bascom Palmer
34	12/12/2020	U Arkansas	U Louisville
35	12/14/2020	Stanford Ophthalmology Advanced Research Residency Program (SOAR)	U California - San Diego	Wills Eye Residency Program at Jefferson
36	12/15/2020	Sinai Hospital -Baltimore	Wills Eye Residency Program at Jefferson
37	12/16/2020	Wake Forest U	U California - LA
38	12/17/2020	Sinai Hospital -Baltimore
39	1/4/2021	Baylor College of Medicine/Cullen Eye Institute
40	1/5/2021	Baylor College of Medicine/Cullen Eye Institute
41	1/6/2021	U California - LA
42	1/8/2021	UT Southwestern- Dallas	Baylor College of Medicine/Cullen Eye Institute
Sheet2

Sir - I thank you on bent knee.

I hope you have an excellent day ??

--Abhinav

 

[alansidman]

Thanks for the feedback.