Consolidating Rows in Excel if they reach certain criteria

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3138554%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EConsolidating%20Rows%20in%20Excel%20if%20they%20reach%20certain%20criteria%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3138554%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20am%20trying%20to%20consolidate%20a%20purchase%20history%20by%20a%20manufacturer%20part%20%23.%20Some%20parts%20have%20more%20than%20one%20price%20that%20they%20were%20purchased%20at%20so%20some%20part%20numbers%20will%20need%20to%20have%20more%20than%20one%20line.%20Does%20anyone%20know%20how%20I%20can%20consolidate%20my%20spreadsheet%20so%20that%20if%20the%20manufacturer%20part%20%23%20matches%20the%20price%2C%20all%20rows%20with%20that%20part%20%23%20and%20price%20combine%20into%20one%20line%20summing%20the%20total%20units%3F%20Essentially%20if%20criteria%201%20and%202%20match%20on%20a%20line%20I%20need%20them%20to%20sum%20the%20data%20in%20a%20specific%20column%20and%20combine%20to%20one%20line.%20Any%20suggestions%20are%20appreciated.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3138554%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Visitor

I am trying to consolidate a purchase history by a manufacturer part #. Some parts have more than one price that they were purchased at so some part numbers will need to have more than one line. Does anyone know how I can consolidate my spreadsheet so that if the manufacturer part # matches the price, all rows with that part # and price combine into one line summing the total units? Essentially if criteria 1 and 2 match on a line I need them to sum the data in a specific column and combine to one line. Any suggestions are appreciated.

2 Replies
I've got something similar. I'd like to see a possible fix...

@JamieP440 That could be done with PowerQuery. Connect to the history file(s). Group by part# and price, summing quantities. Works well with very large data sets.

How exactly? That depends on the structure of the data. The link below would be a good starting point to get to know PQ. Section19 in particular, deals with Grouping and summarising data.

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

 

Alternatively, you might be able to use a pivot table. Part# in the Row field, Price in the Column field and Sum of Qty in the Value field