Dynamically select and stack the monthly data

[Allenli]

I have a list of monthly data file between December 2019 to May 2020, I want to be able to dynamically select the monthly data and stack the data on the result as it shows below, is it possible to do? I was able to do the first step, but don't know how to dynamically select the months range. Please see attached for example data.

Raw Data: Ending balance from the data file

Dec-19	Company A	Company B	Company C
Ending Balance	5170	9547	862
Jan-20	Company A	Company B	Company C
Ending Balance	8065	3479	485
Feb-20	Company A	Company B	Company C
Ending Balance	2050	7635	8747
Mar-20	Company A	Company B	Company C
Ending Balance	8059	4192	6106
Apr-20	Company A	Company B	Company C
Ending Balance	3829	5216	1369
May-20	Company A	Company B	Company C
Ending Balance	2198	5939	4807

Step one: Combining and calculating the difference from different monthly files.

Jan-20	Company A	Company B	Company C
Beg Balance	5170	9547	862
Diff	2895	-6068	-378
End Balance	8065	3479	485

Step Two: Dynamically selecting the month on the result. For example, I want to show the data between Dec-19 to Feb-20 or between Jan-20 to Mar-20, how can I do that?

Dec-19	Company A	Company B	Company C
Beg Balance	967	7013	7615
Diff	4204	2534	-6753
End Balance	5170	9547	862
Jan-20	Company A	Company B	Company C
Beg Balance	5170	9547	862
Diff	2895	-6068	-378
End Balance	8065	3479	485
Feb-20	Company A	Company B	Company C
Beg Balance	8065	3479	485
Diff	-6015	4156	8262
End Balance	2050	7635	8747

Or

Jan-20	Company A	Company B	Company C
Beg Balance	5170	9547	862
Diff	2895	-6068	-378
End Balance	8065	3479	485
Feb-20	Company A	Company B	Company C
Beg Balance	8065	3479	485
Diff	-6015	4156	8262
End Balance	2050	7635	8747
Mar-20	Company A	Company B	Company C
Beg Balance	2050	7635	8747
Diff	6009	-3443	-2641
End Balance	8059	4192	6106

[GaneshBo · Accepted solution]

Hi @Allenli 

I hope the attached solution helps. The trick is to transform the data to dynamically manipulate it and do calculations for all the companies. 

If you'd like to select the month, I'd recommend using the filtering logic, which would require you to leave an extra date column (as shown below).

(Note: Since I don't have the data for the beginning balance of December 2019, I left it blank.)

Best,

Ganesh

[Allenli]

Thank you for your reply. I ended up creating a macros to do the calculation and then using the filter to select the date range. Your idea helps a lot on the logic.