Forum Discussion
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...
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.
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.
- Formula in cell F50:
=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))
- Fill the formula across the range.
- Adjust cell references in the formula as needed.
**** Please marks this post as best solution if it solves the issue as well like.