Pivot Table Spaces – Fill with Above (Excel 2002)

Topic

New Reply

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

64BS	1239000	-200

Reply | Quote

Replies

Any chance you could use Access for this?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

> 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!

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote