Help with consolidation

Question

Hi, I would like to consolidate data from several columns that have multiple rows with counts and duplicate item numbers.&nbsp;I have something likes this:&nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp;Item #&nbsp; &nbsp; &nbsp;Quantity&nbsp; &nbsp; Unit Price&nbsp; &nbsp; &nbsp; &nbsp; Description1&nbsp; &nbsp; 5455&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$1.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Red Crayon2&nbsp; &nbsp; 6777&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$40.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Blue Crayon3&nbsp; &nbsp; 6777&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$40.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Blue Crayon4&nbsp; &nbsp; 6777&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$40.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Blue Crayon5&nbsp; &nbsp; 8002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$7.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Orange Crayon6&nbsp; &nbsp; 8003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Yellow Crayon7&nbsp; &nbsp; 8004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$6.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Black Crayon8&nbsp; &nbsp; 8004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 27&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$6.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Black Crayon9&nbsp; &nbsp; 8004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 36&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$6.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Black Crayon10&nbsp; 9234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$70&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Rainbow Crayon11&nbsp; 10556&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$150&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Darkness Crayon&nbsp;I would like to consolidate the item numbers, sum the quantities per item number, consolidate the unit prices by item number and consolidate the descriptions by item number.&nbsp; I am having the worst time with this.&nbsp;&nbsp;Your input, as always, is greatly appreciated and thank you for your time.&nbsp;

lorenzo · Answer

Hi&nbsp;johnsboxftm&nbsp;&nbsp;Next time(s) please provide: Excel version + OS (Windows, Mac...) - Thanks&nbsp;In attached file 3 options:- Power Pivot (Excel &gt;/= 2013/Windows)- Power Query (Excel &gt;/= 2016/Windows)- Excel 365 dynamic array

johnsboxftm · Answer

Lorenzo&nbsp;My apologies, it's Office 2021 and Windows.&nbsp;Here is a copy of the .xlsx.&nbsp; There is nothing sensitive in it, it's just a bunch of Lowe's receipts organized by date, item number, unit price and description.

lorenzo · Answer

johnsboxftm&nbsp;There are 10 sheets in your workbook- Which one contains the data you want to consolidate?- Any pref. between a Power Pivot or Power Query solution?

johnsboxftm · Answer

Whatever is the simplest to learn, haha. And Sheet 3, Columns B, C, D &amp; E to the last row of 260. Sorry, I actually sent you the wrong sheet I had a less messy one. Thank you so much for your help!

lorenzo · Answer

johnsboxftm&nbsp;- Formatted Sheet3 as Table (named&nbsp;Table1)- Renamed Sheet3 as Data (for clarity)See sheet Consolidation&nbsp;Moving forward: when you add/delete/modify data in Table1, right-click somewhere in table Consolidation &gt; Refresh

Forum Discussion

Help with consolidation

8 Replies

Resources