Oct 09 2018 08:02 AM
Oct 09 2018 08:02 AM
Hello,
I need some pivot table help. I’m using Excel 2010 and have scoured google for an answer without success.
I currently have a table that looks like this (sample):
Office | Client ID | Requested Funds |
Central | X8345987 | Mileage Parking |
Northern | X435786 | Parking |
Western | U92345987 | Mileage Car Insurance |
Central | G3405695 | Parking Mileage Car Insurance |
I have the office, client ID, and funds requested. In the funds requested column I used ALT + Enter to list multiple items in the one cell.
The problem is, when I create a pivot table, this is what I get:
Count of Requested Funds | Column Labels | ||||
Row Labels | Mileage | Parking | Mileage | Parking | Grand Total |
Central | 1 | 1 | 2 | ||
Northern | 1 | 1 | |||
Western | 1 | 1 | |||
Grand Total | 1 | 1 | 1 | 1 | 4 |
What I want is excel to separate the list items, and count them individually. For example, instead of providing a count of 1 for mileage and car insurance together, I want a count for mileage AND a separate count for car insurance.
I want my pivot table to look like this:
Office | Mileage | Parking | Car Insurance |
Central | 2 | 2 | 1 |
Northern | 0 | 1 | 0 |
Western | 1 | 0 | 1 |
Can anyone help me do this? Or is it even possible with a pivot table?
Thanks!
Oct 09 2018 09:03 AM
Hi Amie,
To get the count of Requested Funds for each office, you must separate each record in a separate row as below:
Please find the attached file
Regards,
Haytham
Oct 09 2018 09:08 AM
Hi Haytham,
Thanks for the response. I have thousands of entries in my excel data sheet. Is there a fast way to separate each row without having to do it manually?
Oct 09 2018 10:03 AM - edited Oct 09 2018 10:18 AM
This can be done easily by using the PowerQuery!
But since you have Excel 2010, you have to download and install the PowerQuery add-in from here.
After you install PowerQuery, please follow this GIF to learn how to transform all entries.
Hope that helps
Apr 09 2021 06:30 PM