Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot Table Spaces - Fill with Above (Excel 2002)

    Edited by HansV to present data in table format

    Hello again!
    I often utilize a pivot table to summarize data by dept, account number, and amount. As usual, the pivot table states the dept on the first line (see 64BS in the example below) then has multiple spaces below it until the next dept is summarized. I then copy & paste the summarized pivot table data and then run a "fill with above" macro to "fill in" every line below the "64BS" with the "64BS", etc. The dept, acct & amounts are then uploaded into our system as a journal entry. Can anybody think of another way to subtotal the massive data other than a pivot table (the data changes each month, so it's not as easy as creating a vlookup, or subtotal function). Or is there a way to get the pivot table to show the 64BS on every line? Any ideas would be appreciated.
    Thanks!!
    LJM

    <table border=1><td>64BS</td><td align=right>1239000</td><td align=right>-200</td><tr><td align=right>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Spaces - Fill with Above (Excel 2002)

    Any chance you could use Access for this?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Spaces - Fill with Above (Excel 2002)

    Maybe I exaggerated... it's not "massive" amounts of data, however it's usually about 1,300 lines... the pivot table summarizes it into about 250 lines. I'm using Excel to "map" ADP's payroll download into our data format so I can then upload it as a journal entry. I use a lot of vlookup's to map it, then summarize it using a pivot table... also, I don't know much about access, so I'm thinking probably not, however I willingly to look at it as an option if you think it's something I should try.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Spaces - Fill with Above (Excel 2002)

    In Access, it would be a simple matter of creating a totals query that groups on dept and account, and sums on amount. To minimize the impact, you could create a table in Access that is linked to the Excel table you have now, and base the query on the linked table. The query can be exported to .xls or .csv format.

    If you prefer to keep everything in Excel, it would probably be possible to automate the "mapping", creation of the pivot table and running of the fill-down macro, so that creating the upload would be reduced to pushing a button or to.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Spaces - Fill with Above (Excel 2002)

    As it stands now, I have a pivot table that I refresh each month (with the new data) and then do a quick copy & paste, then press a command button to run the "fill with above" macro, so it doesn't take but 15 seconds... I'm thinking that in Excel I've automated it as much as I can. I thought it was worth asking to see if the pivot table had that option...I use them extensively, however I'm still learning new things about them and I thought maybe I just couldn't find this option.
    Thanks Hans!
    LJM

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Spaces - Fill with Above (Excel 2002)

    > it doesn't take but 15 seconds...

    That's not long, from your description I got the impression that it was much more work than that! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    And no, pivot tables have no built-in option to repeat row labels.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Spaces - Fill with Above (Excel 2002)

    Yep... sorry... just attempting to streamline as much as possible... eliminate keystrokes when I can, etc... thought is was worth a try!!
    Thanks again Hans!
    LJM

  8. #8
    New Lounger
    Join Date
    Apr 2001
    Location
    Lititz, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Spaces - Fill with Above (Excel 20

    If none of your accounts are in multiple depts, you could switch acct and dept in the pivot table row - and then modify your upload if necessary.

Posting Permissions

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