Combine duplicate rows and sum the values 1

[Cpreston]

Hi

I have a spreadsheet (Excel 2016) with various columns. I want to Combine duplicate rows and sum the values of the last 2 columns, not all the columns.

Is this possible and if so how. I have used the Consolidate function, but it adds up all of the columns where it can and not just the last columns.

Does anyone know of a way to do this please.

Thanks in advance

 

[SkipVought]

Hi,

How about a good example or upload your workbook.

But it sounds like a PivotTable might do the job from what you said.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

No problem heres is an example sheet. The highlighted codes are the duplicates.

I only want to see on row with the columns I and J sum for each duplicate row, the other columns c to G not summed but left with the figures left as they are (but only showing once)

I tried a PivotTable but could not get rid of duplicates

 

[SkipVought]

In the Pivot Table Fields window, ALL the fields go into the ROWS area except for the last two m3 fields that go into the VALUES area as Sum of...

I get 29 rows of data in my PT.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

TILT!!!

You have empty rows in your table. This is a HUGH mistake!!! FAQ-5184

Get rid of them!

Now I have 115 rows in my PT. Your source data has 155 rows

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's your workbook

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Thanks, I tried the Pivot but got different results to yourself, may well have been the empty rows.

I will take a look, but the result I am sure is correct. Thanks for the help

 

[SkipVought]

It WAS the empty row(s) cuz that’s what bit me, too!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}