How to split comma-separated values for Pivot Table?

1

I have data which has the following format:

Electricity
Electricity, Cooling
Electricity, Heat
Electricity, Heat, Cooling

I want to use this data in a Pivot Table so I can count the number of items. At the moment, it looks like this:

Pivot table

I would like to set the Pivot Table so that it reads like the following:

Row Labels    |    Count of Energy vectors
Electricity   |                          4
Cooling       |                          2
Heat          |                          2

How could I set the Pivot Table to achieve the above (or rearrange the data automatically to get the same result)?

Joseph

Posted 2018-07-31T13:04:33.067

Reputation: 303

1

you need to unpivot your data before you can summarize it. look e.g. here: https://superuser.com/questions/78439/how-to-unpivot-or-reverse-pivot-in-excel

– Máté Juhász – 2018-07-31T13:16:23.910

@MátéJuhász - Thanks, I'll take a look :) – Joseph – 2018-08-01T14:15:05.330

No answers

Asked: 2018-07-31T13:04:33.067

Viewed: 742 times

Active: 2018-07-31T13:04:33.067