Forum Discussion
Data consolidation
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.
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
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.
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
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.
- Pivot Tale doesn't solves the issue,,, the vschiralli was looking to consolidate common data,,, numeric value must be added,,, please check the data he has shared with original post in the second table the expected QTY is 59 for the code RN1601 is SUM of 54+3+2.
I've examined the attached Workbook and the Sheet "Cast-In Plates" the PT also,, you just add few duplicates for example any code like PL2001, on PT the value in cell P3 will remain 263.2419,, even if you refresh the PT!!
The reason is Cell H1 has formula =SUMIF($H$2:$H$1003083,"<>#N/A") ,,, recalculates every time you add/get value in A2:H500 or beyond ,, as soon you REFRESH the PT you find Excel removes the SUM of 420 column coz 420 is SUM of values are in Col H2 downwards.
