Forum Discussion
I have weekly NFL schedule with three slicers to pick from
sf49ers19238597 Did away with all of your colorful tables and restructured the data a bit. Then with the help of PowerQuery and PowerPivot I could create something that come fairly close to your requirement. See attached.
How you made it to works can tell me in detail.
How do I get Power Query?
sf49ers19238597 If you are on Excel 2016 or later, PowerQuery (a.k.a. Get and Transform Data) is integrated in Excel. For the 2010 and 2013 versions it's an add-in.
It goes too far to explain in detail how it all works. There are many resources on line to get you started. For instance:
https://exceloffthegrid.com/power-query-introduction/
A team can be both in the Away and Home column, so you can't slice a table for a team on both columns at the same time. At least, I can't. You need to "flatten" the table.
In PowerQuery you can do this very easily with the Unpivot function. In my original file, I finished it off in PQ as well, but you can do it all within regular Excel, if you don't have to deal with too much data. It involved some copy/paste actions and a few extra formulae. Have added a sheet (Data2) to the workbook to demonstrate it (see attached).
Flattening the data leads to creating two rows for each match. One for the Away team and one for the Home team. Both rows have the same match details (Date, Time and "Match Info", i.e. the text with "Away team @ Home team".
Now you can create a pivot table and add slicers for both Team and WeekNumber. See sheet PT2 in the attached workbook.
