How to consolidate duplicates in Excel where several columns need to remain uncosolidated
I am trying to do a stock count through product stores but have several part numbers that are listed twice (Column A). When I try to consolidate these I recieve the error 'no data consolidated' which I think is caused by Columns B-F (which will always be the same if Column A value is the same).
I want to consolidate the rows where columns A-F are the same, into a singular row with Column G representing the subtotal of Column F for all duplicate rows.
Screenshot of Corresponding Spreadsheet
I have searched the site and though there are people with similar problems, none of the answers have applied to my exact data. I can't use a pivot table because the parts are stored in so many places that it ends up being unreadable (see second attached picture).
Answer
You can do this with a pivot table, you just need to make some adjustments to the display to get it looking like your current set of data.
First, set up a pivot table with the columns A-E in the rows, and column F in the values.
Next go to the Design area, and change the report layout to be Show in Tabular form.
Finally, go to the Subtotals, and select Do not show Subtotals.
This should give you the totals for each, and you should also be able to see if something is out of place in columns B-E, as there will be extra entries for misplaced items.
If you don't care about misplaced items, then the + symbol next to the part number will collapse the other pieces into a single line