It is possible to do this very easily with Power Query/Power Pivot which would be much more efficient and will not require you to create additional columns. If this is an ongoing project, saving each of the data dump files as a csv file will give you the best performance over time. You could also copy/paste the data into the same file for each refresh but that is up to you.
STEP 1 - Save the files in a folder.
For this example, I have created a file from your sample data as well as an additional file in the same format, to illustrate the process. I have saved the files in the same folder, with the file name as the date of the file 190915.csv and 190701.csv (2nd is just an example). Power Query will use the file name to add a file date column to the table as opposed to the user having to add a date column manually to each file.
Note - each file should have the same layout (column headers), the data should be on the first worksheet in the file and saved the same folder. The only other files that should be saved in this folder should be files to be consolidated in this process. Save other files somewhere else. (There are other options that work around this but doing it this way is easiest for beginners).
Sample File 1 Data
Sample File 2 Data
Snip of the files saved in a folder.
STEP 2 - Create a Date table.
We will create a date table, which we will relate to our data table. Open a new Excel file, or the file in which the pivot table should be created. This should not be one of your csv files. I have created a new Excel file (.xlsx) and saved it in the data files' parent folder.
In the Excel file, go to Data > New Query > From Other Sources > Blank Query
The query editor window will appear with a blank query displayed. Click Advanced Editor on the toolbar to bring up the Advanced Editor. Copy the existing text and replace it with this code to add a new table of dates, beginning with 1/1/2020 and ending with today. Then click Done. Your will see the dates table in the query editor. In the Query Settings pane, rename your query to q_Dates. I also added a column for the start of each week. (From the ribbon > Add Column > Dates > Week > Start of Week)
let
Source = #date(2010,1,1),
GetListOfDatesThroughToday = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
ConvertListToTable = Table.FromList(GetListOfDatesThroughToday, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ChangeDataType = Table.TransformColumnTypes(ConvertListToTable,{{"Column1", type date}}),
RenameCol1 = Table.RenameColumns(ChangeDataType,{{"Column1", "Date"}}) in
RenameCol1
On the Ribbon, click Close and Load To. The query editor window will close and Excel will be displayed. A pop-up window will display. Choose Connection Only, check Load to Data Model and okay. This is telling Excel to load the data to the data model, we can related it to other data and not to create a new worksheet with the dates table.
Step 3 - Get the CSV data from our folder and make necessary edits (Get & Transform).
Back to the Ribbon, choose Data > New Query > From File > From Folder. Browse and select the folder in which your CSV files are saved and click okay. Now Excel will display your files that are in the folder. Click the down-carrot on Combine and choose Combine & Edit. You will then see a preview of the first data file. Just click okay.
The Query Editor Window will launch again and will navigate you to the query with the consolidated data. Instead, select query for the sample file and make your edits here. This tells Excel to make the same edits to all files in the folder.
Change the Created Date and Termination Date columns to date data types. Highlight the two columns and on the ribbon, choose Transform > Data type > Date.
Then go back down to the query that has the sheets consolidated and add a new column for the report date. (Ribbon > Add Column > Custom Column) Paste this in the formula window after the equals sign and click okay.
#date(Number.FromText(Text.Middle([Source.Name], 0, 2))+2000, Number.FromText(Text.Middle([Source.Name], 2, 2)), Number.FromText(Text.Middle([Source.Name], 4, 2)))
Now we are ready to add a new column to determine whether or not the agent was active as of the report date.
[Terminated Date] = null or [Terminated Date] > [Report Date] then "Active" else "Inactive"
Then Close and Load To (connection only, load to data model).
STEP 4 - Relate the Tables Using Power Pivot
From the Ribbon, go to Power Pivot > Manage. This is an add-in that you may need to enable.
Click here for more info if you do not see a Power Pivot tab on your ribbon.
https://support.office.com/en-us/article/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8
The Power Pivot window will open and you will see one tab for each query/connection.
From the Power Pivot ribbon, go to Diagram View to see your connections as diagrams. Create a relationship between the dates table and your data table by clicking on the Date field of the Dates table and drag/drop onto the Report Date field in the Data table.
Then from the ribbon, insert a pivot table and design as you would like. Relationships allow you to pull data from multiple tables into one pivot. Just save new files in the same folder and refresh.
