Forum Discussion

vschiralli's avatar
vschiralli
Copper Contributor
Nov 10, 2020
Solved

Data consolidation

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 PR...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Nov 10, 2020

    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.

vschiralli's avatar
vschiralli
Copper Contributor
Nov 10, 2020

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

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 10, 2020

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.

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Nov 11, 2020
    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.
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Nov 11, 2020

      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.

      • Rajesh_Sinha's avatar
        Rajesh_Sinha
        Iron Contributor
        Nov 12, 2020

        Riny_van_Eekelen 

         

        No Excel doesn't updated the PT.

        What I've done.

        • In Sheet CiPo2 to get new value, I've updated cell D23 with new value 400, and got 102 Kg in cell H23.
        • Now in Cast-In Plates sheet excel updated the cell value with 102 kg in cell H12 & H1 with 522 Kg ( is formula bond ), but in PT the corresponding value in cell P3 remain 321.7401.

        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.

Resources