Nov 10 2020 06:13 PM
I have some data that I would like to consolidate. The data currently looks like this
(This data is copy and paste-linked from separate worksheets to create a large list of parts)
ITEM | PROFILE | LENGTH (mm) | WIDTH (mm) | GRADE | QTY | 420 kg |
RN1601 | N16 | 360 | 0 | _300PLUS | 54 | 31 kg |
RN1601 | N16 | 360 | 0 | _300PLUS | 3 | 2 kg |
RN1601 | N16 | 360 | 0 | _300PLUS | 2 | 1 kg |
But I want it to combine when it detects the same item number, profile, length, width & grade and add up the quantities and weight so that it looks like this
ITEM | PROFILE | LENGTH (mm) | WIDTH (mm) | GRADE | QTY | 423 kg |
RN1601 | N16 | 360 | 0 | _300PLUS | 59 | 34 kg |
Does anyone know if this is possible and how to do it?
Thank you
Nov 10 2020 09:08 PM
Consider using a Pivot Table or you may use Power Query to group the data otherwise there are more than one way to achieve that.
Nov 10 2020 09:27 PM
@Subodh_Tiwari_sktneer Thank you. I've tried pivot table but it didn't get the result i wanted. It made it quite complicated to look at. The people I'm sharing this data with need to keep it as simple as possible. This is what the pivot table looks like, which isn't really the outcome I'm after
I've just tried playing with power query, but it doesn't seem to group the data together.
I'm not sure if you can see that, but the top 3 rows should be combined into 1 element (the Item numbers are the same) and the qty should be tallied up to 59 total. Rather than 3 rows for the same item
Nov 10 2020 09:58 PM
What you are trying to achieve, the DATA CONSOLIDATION doesn't suits, because it summarizes the data and applies the selected aggregate function on all numeric values, other your sources data does have one ALPHANUMERIC column comprises the unit Kg.
Therefore I would like to suggest SUMPRODUCT with trick sums Alphanumeric column also.
=SUMPRODUCT(($A$42:$A$46=$A50)*($B$42:$B$46=$B50)*($C$42:$C$46=$C50)*($D$42:$D$46=$D50)*($E$42:$E$46=$E50)*(SUBSTITUTE(F$42:F$46,"Kg","")+0))
**** Please marks this post as best solution if it solves the issue as well like.
Nov 10 2020 10:37 PM
@vschiralli You can achieve this with a Pivot table, but you need to change the default formatting a little bit. See attached.
And with regard to power query, you connected to the table and data types were set. You haven't yet applied any step to transform/group the data.
Nov 10 2020 11:17 PM
@Riny_van_Eekelen thank you. I've downloaded your sample and that is pretty much what I want to achieve. I've tried again using the pivot table, but I don't know what I'm doing wrong. It still spits out data like crazy
I've attached my material list for the project. Each worksheet is for each individual assembly drawing. Each worksheet uses vlookups and hlookups with dropdowns to select items which come from the worksheet "Materials". I have created this entire spreadsheet myself, which has been quite a headache for the most part over the past couple of years. This is really the last part that I want to change
Nov 10 2020 11:23 PM
Solution@vschiralli You were just a few clicks away from the desired output. On the Design tab for the pivot table, select "Show in Tabular form" in the Report Layout group. and choose "Don't show Subtotals" in the Subtotals group. See attached.
Nov 11 2020 10:47 PM
Nov 11 2020 11:48 PM
@Rajesh_Sinha Not sure I follow. See attached. Added a duplicate for PL2001 via sheet CiP02, though with a different Qty and thus a different weight. The total weight and PT updates as expected.
Nov 12 2020 01:13 AM
No Excel doesn't updated the PT.
What I've done.
And when I refreshed the PT , Excel removes the Column SUM of 497, because the new value in PT cell P1 is 522 which formula bond SUM of 522, which Excel reads from cell H1, and is not the part of the PT, since PT has SUM of 497 as VALUE !!.
Check the attached file, also check the cells I've used in Sheets.
Nov 10 2020 11:23 PM
Solution@vschiralli You were just a few clicks away from the desired output. On the Design tab for the pivot table, select "Show in Tabular form" in the Report Layout group. and choose "Don't show Subtotals" in the Subtotals group. See attached.