Pivot Table Help - Counting multiple items in a cell individually

Deleted
Not applicable

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

Parking

Mileage
Car Insurance

Parking
Mileage
Car Insurance

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!

4 Replies

Hi Amie,

 

To get the count of Requested Funds for each office, you must separate each record in a separate row as below:

Pivot Table.png

 

Please find the attached file

 

Regards,

Haytham

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?

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.

 

SplitColumnByDelimiter_GIF.gif

 

Hope that helps

You can't imagine how helpful your comment was to me, thanks a lot!