Have data in multiple rows needs to be combined and extra rows deleted

[Modnar1]

So some orders will have multiple line items and that creates a duplicate of the sales order number, all i need is a total cost of goods sold for the whole order. So in the first highlighted example you can see they purchased 2 ea of 2 different items the cost of one being 0.03 and the second 0.10 so the total cost is 0.26 for the order in total. I need a field with an updated cost and to delete the extra rows. Please bear in mind I am working with large sets of data and need this to be a function or something that works more or less automatically without me having to try and find all orders with multiple lines.

[#2]

@Modnar1 Perhaps Power Query can help. The attached file contains a query that calculates total CoGS by order number. If that's not what you had in mind, please clarify how the end result should look like.

[#3]

so is there a way to save a power query for use later? This is part of a report I need to process on the regular and generally what I do for these is create a spreadsheet with tabs where i just dump the raw data and then a single output page with all the data i need to see

[#4]

@Modnar1 That's exactly where Power Query comes is. You connect to a data source and perform all sorts of transformations. Each of these these are "recorded" in their own step and will be executed every time you press Refresh. In the example I uploaded earlier, I connected to the data that I named "myData". That's what you can see in the very first applied step.

Source = Excel.CurrentWorkbook(){[Name="myData"]}[Content],

But you can connect to a table, a CSV file, a web-page and many more sources.

You may remove everything in A1:I17 in Sheet1 and paste new data somewhere else. But the structure must be the same as the original (i.e. the column names must be the same), but you can paste thousands of rows. As long as you name the entire data set "myData", you can Refresh the query I created. Lear more about PQ in the link below.

https://exceloffthegrid.com/power-query-introduction/