Results 1 to 3 of 3

Thread: Filling in lines on pivot table

  1. #1
    Neophyte katiepie's Avatar
    Join Date
    Mar 2018
    Location
    IL
    Posts
    1
    Articles
    0
    Excel Version
    2016

    Filling in lines on pivot table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    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?
    Thanks,
    Attached Files Attached Files

  2. #2
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016
    Katie

    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
    Attached Files Attached Files

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,999
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by katiepie View Post
    These are my first two issues.
    Click image for larger version. 

Name:	2018-06-08_204801.jpg 
Views:	15 
Size:	83.7 KB 
ID:	8035
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •