CODEWITHSUNDEEP
excelduplicatesmultiple-columnsrowsconsolidation
ellie
ellie

Reputation: 11

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).

Screenshot of Confusing Pivot table

Upvotes: 0

Views: 21376

Answers (2)

Daniel
Daniel

Reputation: 66

SUMIFS() Should solve your issue here. You can use something like =SUMIFS(F:F,A:A,A2,B:B,B2,C:C,C2,D:D,D2,E:E,E2) if it is essential that A-E match. This will give you a Column G with the result you are after however this way will also give you multiple rows.

Column G Solution

IF B-E are always the same for a Column A value then you can always paste the unique Column A values in a separate sheet and then use =SUMIFS(Sheet1!F:F,Sheet1!A:A,Sheet2!A2)

and display something like this

Separate Sheet Answer

Upvotes: 1

SeanC
SeanC

Reputation: 15923

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

Upvotes: 1

Related Questions