• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting Data from Pivot Tables

I have what must be a common problem. The attached file has a sample Pivot Table. In that file, 182 refers to the company number, and there are a lot of companies (only two are used here). I am using a Pivot Table like this as a database from which to extract data to populate models for each company. I need to extract the amounts by company number, but the amount and company number are on separate rows. So, for example, I need to find Bad Debt Expense (row 6) for company 182 (row 8). I have tried SUMPRODUCT and GETPIVOTDATA functions, but I think they want proper database format (expense and company number in the same row).

Any solutions?
Paul
 

Attachments

  • PT Example.xlsx
    16.1 KB · Views: 1
Hi Paul ,

Can you not use a helper column where the category is repeated , as in column P in the attached file ?

Narayan
 

Attachments

  • PT Example.xlsx
    17.4 KB · Views: 1
What you're suggesting turns the pivot table into a proper database, where every row would have a category name and a company name. As the data will be refreshed each month and the list will grow or shrink, I'd have to copy the helper formula down. A macro could automate that. This seems like an elegant solution, though. Thanks so much!
 
Back
Top