Forum Discussion
Using multiple fields in pivot table rows
Thanks for the sharing
- I asked actual Table. In your initial pic. it goes at least until col. X. What you shared goes till col. P
This isn't a problem IF in what you shared we have the columns we need for the various calc. in the PivotTable - I highlighted them in green (hided the others) according to my understanding - (In your comments) Open to resolving with a different format for entering customer names
You can keep recording as you do IF: 1) Customers (on the same row) are always delimited with a comma; 2) SalesRep is always after the 1st opening parenthesis - In sheet 'Expected' I highlighted some variances + add. comments. On the $ amounts I think I'm right
How from your Table to the PivotTable
This is done with Get & Transform aka Power Query. Basically (all done with the UI only, only renamed the steps so you can easily follow & understand):
- Select required columns only
- Split Customer(s) to new rows on delimiter comma
- Split Customer and SalesRep in 2 columns on 1st opening parenthesis
- Remove [SalesRep]
That's it for now. But this isn't good yet as there's something you didn't say and I couldn't figure out - see below
Total Jobs Won
Currently unresolved/off vs. your expectation. Once we've done the above transformations with Power Query, we get:
Consequently (assuming I understood how to count Jobs Won) in the PivotTable we get:
#1 To update the query - you will have to explain exactly how to identify a Contractor is "linked" to only 1 Customer (and to which one) when we have > 1 Customer on the same row/record (I could make an assumption but I don't like)
#2 Is it possible you have > 1 Contractor on the same row? Something like:
- If you do, how are they delimited (comma again)?
- Cf. #1, how do we map Customers and Contractors?
I'm gonna be off for +/- a week. There are other contributors to this community who can help you further with this
PS: If it was me I would take this opportunity to clean/rename the Table headers... (the query would need to be updated accordingly - easy)
- I asked actual Table. In your initial pic. it goes at least until col. X. What you shared goes till col. P
Based on the information you've provided and the image you sent, here's how you can create a pivot table in Excel to summarize sales data with multiple customer entries per job:
1. Split the "Customers Quoted" Column:
- You don't need to use "Text to Columns" as it would create a hierarchy. Instead, use the following formula in a helper column (e.g., column Q) to split the comma-separated customer names into separate rows:
- Replace A2 with the cell reference containing the first customer list. Drag the formula down to apply it to all rows.
2. Create the Pivot Table:
- Select the entire data table, including the helper column.
- Go to the "Insert" tab and click "PivotTable."
- Choose the location for the pivot table (new worksheet or existing one).
3. Configure Pivot Table Fields:
- Drag the "Jobs" field to the "Rows" area.
- Drag the "Contractor 1," "Contractor 2," etc. fields to the "Columns" area.
- Drag the helper column (e.g., "Customers Quoted (Split)") to the "Filters" area.
4. Expand All Customers:
- Click the down arrow next to the "Customers Quoted (Split)" field in the Filters area.
- Check the box next to "Select All Items." This ensures all customers are included in the pivot table.
5. (Optional) Calculate Total Quoted Amount:
- In the pivot table, right-click on any value cell and select "Summarize Values" > "Sum." This will calculate the total quoted amount for each combination of job, contractor, and customer.
Explanation:
- The helper column splits the comma-separated customer names into separate rows, allowing each customer to be treated independently in the pivot table.
- Placing the "Jobs" field in the Rows area groups the data by job.
- Placing the contractor fields in the Columns area shows the breakdown by contractor.
- Expanding all items in the "Customers Quoted (Split)" filter ensures all customers are considered in the calculations.
- The optional sum calculation provides the total quoted amount for each combination.
With this approach, your pivot table will display each customer on the same level, grouped by job and contractor, without creating a hierarchy