Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting data from a PT (Excel 2003)

    I have a 45k-row table of book and tax retirement transaction records. I have pivoted it by column A = asset #, B=Month, C=company and the data is COST in two column fields, CORP and TAX. Here's the problem. Our brilliant software splits CORP retirements apart and spreads them over different companies' tax books. My PT returns what I want to see: a retirement that was done in April on one corporate book but the cost got split among more than one company's tax book. See below, where the retirement on company 1020 for 840.51 was split up on the tax books to company 1020 and 1104! Example file attached. The majority of the rows are fine....one asset/one retirement amounts/one company on both books, but there are quite a few spread around. I have to get a grip on them.

    My question is: how can I filter only this type of trransaction out of the Pivot table...or should I take a different approach completely? Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting data from a PT (Excel 2003)

    I don't understand completely and your example PT is only 2 rows (and has no source) so I am not clear what to make of it.

    If you can do it with filtering, why not use an autofilter?

    Another approach would be to change the source data to perhaps create a new column that (somehow) figures out the "single company" value and uses just this one value. Then if you use this column for the PT, they will not be split.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting data from a PT (Excel 2003)

    The file is large so I tried to give a sample. Attached is an example with data. If an advanced filter can get at this, fine..anyway I can extract from the larger list those assets whose retirements on GAAP books were split up on TAX books is fine. In the attachd, assets 3492 and 3493 are well-behaved. But 3495 can show up on different tax books than the GAAP book from which it starts. Thanks again for any ideas.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting data from a PT (Excel 2003)

    I am still not sure what the problem is. What do you want the PT look like?

    What are you trying to group together? Do you want the "asset_num" without the "CO"? You have the subtotals or you can get rid the CO field in the PT.

    Please elaborate.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting data from a PT (Excel 2003)

    My problem is that that 3495 should behave just as 3492 and 3493 ...one company on GAAP and one company on Tax. I can't retire an asset on one company's GAAP books for financial reporting and then turn around and report the same asset on 3 tax books. So I have to adjust the reports we are gettomg by going and getting those tax retirement amounts and putting them back into the correct tax book...the one the GAAP retirement occured on. I don't care how I get that number: pivot, advanced filter, macro, whatever. For instance I have to get the tax amounts in G20 and G22 back to line G21, where the cost is on the GAAP books. (And what is ths !@#$% popup doing interrupting my typing! Twice in 5 minutes!). How can I cull such transactions out of 45,000 lines most of which are "OK" like 3492 and 3493. That's the third popup since I began typing this.
    Thanks.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting data from a PT (Excel 2003)

    It is still not clear to me what you need to do to "cull such transactions".

    My best guess would be to use a formula or a macro to insert a "New CO" column in your source data. This should use whatever criteria (which is unclear to me, so I have no suggestions, this is partly what I need clarification on) and make a column of "New CO". Then use this "New CO" column in the pivot rather than the "CO" Your PT will be what you are after.

    Steve

Posting Permissions

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