Feb 08 2022 10:06 AM
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.
Feb 08 2022 12:09 PM
Feb 08 2022 10:12 PM - edited Feb 08 2022 10:17 PM
@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