Hello,
I have some financial info from various different customers, which has been input into a spreadsheet with each individual purchased item input. However, I need to extract only the monthly totals for all items purchased for each customer (i.e. not itemised but a total for all).
BUT there is over 14 months' worth of data (and several customers!) so I am looking to find a way to shorten this exercise quickly without having to manually do too much or, worse, have to manually input each month's total myself. Is there a function or method to quickly extract this data and alter the existing sheet without also having to do too much backwards and forwards (i.e. cut and paste into another new sheet, etc.)?
TIA,
FP
Extracting monthly totals per customer
-
- 2StarLounger
- Posts: 130
- Joined: 12 Aug 2020, 08:40
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extracting monthly totals per customer
Shouldn't this be in the Excel forum?
Could you attach a small sample workbook (without sensitive information)?
Could you attach a small sample workbook (without sensitive information)?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 130
- Joined: 12 Aug 2020, 08:40
Re: Extracting monthly totals per customer
Ah! Sorry yes - I had thought I had clicked on the Excel one! I will move it over to there now.
I can send a screenshot meantime - will organise to send...
I can send a screenshot meantime - will organise to send...
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extracting monthly totals per customer
I have moved this thread from the Word forum to the Excel forum.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Extracting monthly totals per customer
You were asked about Excel example not a screenshot
anyway you can try Power Query : Group by month and Sum to MonthTotal
anyway you can try Power Query : Group by month and Sum to MonthTotal
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Extracting monthly totals per customer
Here an example using ADO:
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Extracting monthly totals per customer
Power Query
(however you can use Pivot )
excel file contain extended version
(however you can use Pivot )
Code: Select all
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Month = Table.AddColumn(Source, "Month", each Date.Month([Date]), Int64.Type),
Grp = Table.Group(Month, {"Person", "Month"}, {{"MonthTot", each List.Sum([Sales]), type number}}),
Asc = Table.Sort(Grp,{{"Month", Order.Ascending}})
in
Asc
You do not have the required permissions to view the files attached to this post.