Merging data across duplicate rows

[atishk87]

Hi all,

I have a report I receive on a weekly basis where it lists customers performance/data over 271 columns and over 1000s of rows.

In this data there are many lines which are duplicated for customers as they have more than one account number. Is there a way of merging/summing this data together and pulling unique lines on say an new "Output" sheet.

The data looks like this.....(columns 1 - 19 is customer information which does not need to be merged)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Customer name[/TD]
[TD]Acc no[/TD]
[TD]country[/TD]
[TD]sales person 1[/TD]
[TD]sales figures 1[/TD]
[TD]sales figures 2[/TD]
[TD]sales figures 3[/TD]
[TD]sales figures 4[/TD]
[/TR]
[TR]
[TD]aUK[/TD]
[TD]a[/TD]
[TD]10[/TD]
[TD]UK[/TD]
[TD]mark[/TD]
[TD]156[/TD]
[TD]96[/TD]
[TD]34[/TD]
[TD]87645[/TD]
[/TR]
[TR]
[TD]bSpain[/TD]
[TD]b[/TD]
[TD]15[/TD]
[TD]Spain[/TD]
[TD]john[/TD]
[TD]213[/TD]
[TD]5[/TD]
[TD]95[/TD]
[TD]56435[/TD]
[/TR]
[TR]
[TD]cItaly[/TD]
[TD]c[/TD]
[TD]16[/TD]
[TD]Italy[/TD]
[TD]matt[/TD]
[TD]81[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]43521[/TD]
[/TR]
[TR]
[TD]cItaly[/TD]
[TD]c[/TD]
[TD]19[/TD]
[TD]Italy[/TD]
[TD]ash[/TD]
[TD]163[/TD]
[TD]3[/TD]
[TD]173[/TD]
[TD]43214[/TD]
[/TR]
[TR]
[TD]eGermany[/TD]
[TD]e[/TD]
[TD]26[/TD]
[TD]Germany[/TD]
[TD]steve[/TD]
[TD]189[/TD]
[TD]19[/TD]
[TD]16[/TD]
[TD]48943[/TD]
[/TR]
</tbody>[/TABLE]

Based on the unique ID is there a way of copying unique rows over to a new sheet and summing data from duplicate rows together and outputting results onto a new sheet.

Thank you in advance for your help.

 

[Ken Cowen]

Seems like you need a pivot table.

Ken