Forum Discussion
Consolidating Rows in Excel if they reach certain criteria
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
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
- I've got something similar. I'd like to see a possible fix...
