Search code examples
excelpivot-table

Combining fields in pivot table

I'm trying to use data which looks like this:

Client Workstream Project
Alpha a; b; c A
Beta a; d; e B
Gamma b; c; e C
Alpha d D
Beta a; c; e E

To produce a pivot table that functions like this (where I don't care about additional columns like 'Project' used as an example):

Client a b c d e
Alpha 1 1 1 1 0
Beta 2 0 1 1 1
Gamma 0 1 1 0 2

Essentially, create a pivot table out of 'client' and 'workstream', but with 'workstream' separated by the semicolon delimiter. I could achieve this if I expanded out the table by making each workstream its own row, but I want to avoid this because it would make the table very large with a lot of redundant information, and also because I don't see a way to split text to rows (as opposed to columns).

I have tried splitting text into columns and creating a pivot table using these new fields in the 'columns' area of the pivot table, but this creates a huge mess. When I create a standard pivot table with the Client and Workstream as fields, this of course doesn't combine the different 'letters' of the workstream in a sum and created a column for each set of workstreams

Solution

  • For other methods to split data look at this question and the three examples. stackoverflow.com/q/72210897/5091720

    Steps for Excel's Power Query

    1. Click on Data>"From Table/Range"
    2. Remove columns "Project"
    3. Split Column by Delimiter and choose advance and choose rows
    4. replace spaces with nothing
    5. click the "Close & Load To..." choose pivot table
    6. Make you pivot table with "Client" and "Workstream"
    7. Pat yourself on the back.
    Row Labels a b c d e
    Alpha 1 1 1 1
    Beta 2 1 1 2
    Gamma 1 1 1
    Grand Total 3 2 3 2 3