View Full Version : Filling in lines on pivot table

2018-05-11, 06:21 PM
I have created a pivot table that we send to our bank for payments. As of right now there is still some manual manipulation we have to do. The report is a re-freshable report that pulls from our database and then we copy and hard paste the pivot table into a new excel sheet, manipulate what we need and then upload it to the bank. I would like to take out some of the manual work if possible. In the first example (attached example check sheet 1) is how it comes in. The second example is what I would like it to look like. I was wondering if it is possible to have this happen automatically in the pivot table.
Column A needs to be filled out all the way down with every line that has a TRANNO number. Column B needs to be filled out on every single line. These are my first two issues.
Does anyone know if there is a way to do this?

Ed Kelly
2018-06-05, 02:20 AM

Get data/Power Query will take care of all needs that you have. Think it would be best to set things up as an excel file reading from a folder and on a daily basis and each new download would be saved to that folder and yesterdays download archived out of the same folder. Once built it would be a 5 second refresh (button in Power Query) to consistently produce the result you need and it will take about 10 minutes to build depending on exactly what you need done to each dimension (data column) within the original table.

I have attached what I believe are the first 2 steps that you flagged (in this instance done within the Excel file supplied however if multiple users are involved then can be more prone to errors created by the user accidently). As I said earlier this is a 10 minute job, then all you need is to refresh daily to get a consistent result. Happy to build it for you (of course no charge) if you can tell me precisely what you need on a column by column basis or you can learn Power Query and handle it directly. (Note there is already a question on column 1 PAYMTHD is everything below chk a chk payment and is everything below DAC a DAC payment?)

PS you refer to your report as a pivot table when I think you mean a Table and thus I believe you will get more help if you post to the power query section

2018-06-08, 09:53 PM
These are my first two issues.
The two workbooks seem to be from different data sets, so it's difficult to see what changes have been made.
Provide before and after scenarios for the same data.