I have an eight column spreadsheet, with over 2000 rows to date with Date, Supplier, Net, VAT and Gross columns and I need to show in a separate tab the total Net, VAT and Gross amounts for each month for each individual supplier.
I have tried many ideas and run out of thoughts. I am definitely not an expert”
Any suggestions welcome
Thank you
Hello,
create a pivot table according to the following example layout
See the following sample file:
Pivot-Supplier.ods
Note(s):
Date (I’m not aware of a simple way to aggregate Dates into Month on a pivot table)Hope that helps.
Thank you very much for your help. I have never used Pivot tables before but it has solved my problem! Your image was a great help
A case for a pivot table.
First insert another column to your data, name it Month and, assuming your Date column is column A and Month column is B, use the formula =YEAR(A2)*100+MONTH(A2) in cell B2 and copy the cell to the clipboard and paste it down to the end of your data to calculate a month serial number for each row.
Then place the cell cursor somewhere in the data cell range and invoke menu Data → Pivot Table → Create, the data range gets selected and is asked for confirmation, OK.
In the Pivot Table Layout dialog drag&drop fields Month and Supplier (in that order) to Row Fields and fields Net, VAT and Gross to Data Fields. The default data operation is Sum. OK.
That’s it. The pivot table is created on a new sheet. To refresh when data is altered do a right mouse click on the pivot table and from the context menu select Refresh. Or select Properties to rearrange the pivot table layout.
Thank you very much for your help. I have never used Pivot tables before but it has solved my problem!