Consolidation of tables specifying the number of repetitions?

Question

excel

There is a folder with tables.
Each table has two columns: code and amount.
You need to create a pivot table with three columns, where all the codes are collected from these tables and each code indicates how many times it occurred and the sum of the amounts.
That is:
Table 1
111 2
555 40
222 4
Table 2
111 2
555 10
222 6
123 6 555
20
Table 3
555 10
222 5
555 30
Summary:
111 2 4
555 5 110
222 3 15
123 1 6
in the folder automatically fell under the consolidation.
While I'm trying the "data consolidation" button, almost what I need, but
- new tables need to be added manually,
- does not show how many times the code has been encountered,
- despite the pop-up question "update?" in fact, I don’t see an update.
This, as I understand it, is in the direction of macros? Or all the same it is possible to solve regular functionality?

Answer 1

This problem can be solved by regular functionality. Through PowerQuery (depending on the version of Excel, this add-in is already included or you just need to download and install it in Excel), collect data from the folder. Further, through the usual pivot table, consider the number and amount.
When changing files in a folder, simply click on the update of the pivot table, and all data will be updated.