Need totals for each customer, data has duplicates for different jobs

[TessaJ]

I have a data set that has duplicate customers as the original data pulled was based on each month. I'm needing to create a list of each customer with each job/invoice number, job profit. Is there a way to have excel format that instead of going through 1500 lines of data and doing it manually? See sample below. Any help is greatly appreciated!

Customer	Date	Job Number	Total Sale		Job Profit
Customer 1
	1/28/21​	NO GO	6465​		5600.70
	1/28/21	33916	$9,423.53		3058.60
			Total	$15,888.53
Customer 2
	1/15/21	33497	$2,600.00		2600.00
			Total	2600​
Customer 3
	1/26/21​	33645	$995.00		-192.47
	1/27/21​	33789	2275​		1013.32
			Total	3270​
Customer 4
	1/21/21	33519	$18,873.85		18873.85
	1/21/21	33519	$28,507.05		28507.05
			Total	47380.9​
Customer 5
	1/20/21	33796	$3,575.00		1337.51
	1/20/21	33796	$3,325.00		1025.01
	1/20/21​	33796	$1,295.00		149.92
			Total	8195​
Customer 6
	1/7/21​	33357	1961​		1961.00
	1/8/21​	33329	426​		426.00
	1/15/21​	33493	4335​		4335.00
	1/27/21​	33718	4175​		1430.94
	1/27/21​	33718	4175​		1982.19
	1/28/21​	NO GO	No Go		0.00
			Total	15072​
Customer 1
	1/28/21​	NO GO	6465​		5600.70
	1/28/21	33916	$9,423.53		3058.60
			Total	$15,888.53
Customer 2
	1/15/21	33497	$2,600.00		2600.00
Customer 5
	1/20/21	33796	$3,575.00		1337.51
	1/20/21	33796	$3,325.00		1025.01
	1/20/21​	33796	$1,295.00		149.92
			Total	8195​
Customer 6
	1/7/21​	33357	1961​		1961.00
	1/8/21​	33329	426​		426.00
	1/15/21​	33493	4335​		4335.00
	1/27/21​	33718	4175​		1430.94
	1/27/21​	33718	4175​		1982.19
	1/28/21​	NO GO	0​		0.00

 

[alansidman]

If this is what your expected results should look like then the following Mcode provides this. This is a Power Query Solution

Book14
	H	I	J
1	Customer	Job Number	Total Profit
2	Customer 1	NO GO	11201.4
3	Customer 1	33916	6117.2
4	Customer 2	33497	5200
5	Customer 3	33645	-192.47
6	Customer 3	33789	1013.32
7	Customer 4	33519	47380.9
8	Customer 5	33796	5024.88
9	Customer 6	33357	3922
10	Customer 6	33329	852
11	Customer 6	33493	8670
12	Customer 6	33718	6826.26
13	Customer 6	NO GO	0
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Customer"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Customer] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Date"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Total Sale] <> null and [Total Sale] <> "Total")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Column1", "Total Sale"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Customer", "Job Number"}, {{"Total Profit", each List.Sum([Job Profit]), type number}})
in
    #"Grouped Rows"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

		​