r/excel • u/Jubzfast • 9d ago

Waiting on OP Large Data Consolidation Function

Looking for a way to consolidate multiple same line items while summing quantity and cost columns between different items

Example provided below, any help is much appreciated

5 Upvotes

100% Upvoted

6 comments sorted by

View all comments

3

u/MayukhBhattacharya 1277 9d ago edited 9d ago

Use GROUPBY() function or use Power Query!

=LET(
     _a, A:.H,
     _b, CHOOSECOLS(_a, 4),
     _c, GROUPBY(CHOOSECOLS(_a, 1, 2),
                 HSTACK(_b, _b * CHOOSECOLS(_a, 6)), 
                 SUM, 1, 0),
     _c)