How to group this in Pivot Tables

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
142
Office Version
  1. 365
Platform
  1. Windows
https://1drv.ms/x/s!AvjBsEPEq12ngTpjIV0Ri5AcMKZa?e=0sU8cn

I have a table of data.
First column = company names.
Other columns = invoice amount aging by days
E.g. column B = 1 - 010 means invoices overdue between 1 day and 10 days.

I am summarizing the info in a PV.

Suppose I want to create a column showing invoices overdue between 1 day and 30 days.
I could either create a helper column on the table (adding columns b and c) or I could use Fields, Items, & Sets in the Pivot Table ribbon and create the column in the PV.

Which method would you guys recommend for this exercise? Is there a more solid method?

Thank you :)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
try with PowerQuery

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {"Customer Name"}, "Attribute", "Value"),
    IF = Table.AddColumn(UnPivot, "Invoices", each if [Attribute] = "1 - 010" then "1 - 30" else if [Attribute] = "011 - 030" then "1 - 30" else "031 - Above 180")
in
    IF[/SIZE]

then create PivotTable

Sum of ValueInvoices
Customer Name1 - 30031 - Above 180
Company 1
122​
304​
Company 2
25​
404​
Company 3
104​
225​
Company 4
52​
285​
Company 5
194​
390​
Grand Total
497
1608
 
Last edited:
Upvote 0
Beautiful. Thank you Sandy, I appreciate it :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top