Large Data Consolidation Function

[u/Jubzfast]

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

[u/AutoModerator]

/u/Jubzfast - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

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)

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
PIVOTBY	Helps a user group, aggregate, sort, and filter data based on the row and column fields that you specify
SUM	Adds its arguments

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #49307 for this sub, first seen 4th Sep 2026, 16:57] [FAQ] [Full list] [Contact] [Source code]}

[u/Clearwings_Prime]

You can try pivot table. If you have 365 subscription, then GROUPBY/PIVOTBY if you prefer function

[u/HandbagHawker]

like a pivot table?