Invoicing: Pivot Table CSV BreakdownUpdated 9 months ago
CSV Breakdowns of each invoice are uploaded to your Shared Drive that includes an itemized list of your charges
You can then take the .csv file and extract information in the following ways:
Option 1: Top Level Breakdown With Pivot Table
This option will not separate subtypes of the same line item but is simple and easy to do. The most notable limitation of this route is that Standard and Ancillary item picks will be combined.
Step 1: Insert a Pivot Table
- From the dropdown menu, select "Import", then "Pivot Table"
Step 2: Select Rows
- select Column D, "Type (charge)"
Step 3: Select Values
- select Column I, "Total (charge)"
Option 2: Advanced Breakdown with Pivot Table
This option separates all line items into their respective categories but requires a small amount of extra work to get to the final product.Step 1: Merge the Fee & Type Columns:- Concatenate the "Fee (charge)" (Column C) and "Type (charge)" (Column D) fields.
- =C2&"/"&D2 OR =concatenate(c2,"/",d2)
- Step 2: Aggregate charges
- Create a new column with a filter, using this reference sheet and the column from Step 1
- =FILTER('Aggregate Reference Chart'!B:B,'Aggregate Reference Chart'!A:A=J2)
Step 3: Aggregate charges
- From the dropdown menu, select "Import", then "Pivot Table"
- For Rows, select the column you've created in Step 2.
- For Values, select column D, "Total (charge)"
