CODEWITHSUNDEEP
excel
zurik
zurik

Reputation: 513

consolidate values on unique column

I have an excel worksheet with 4 columns (Excel 2011)

id   item_name   owner   amount
1    House       David   500
2    Land        Ernest  600
1    House       David   400
3    Car         Frank   1000
2    Land        Ernest  700

I would like to remove duplicate rows based on the unique identifier, id in this case so that my sheet looks like this

id   item_name   owner   amount
1    House       David   900
2    Land        Ernest  1300
3    Car         Frank   1000

I have a worksheet with close to 113282 rows, the consolidate function can work this out well however, it requires me to manually use a pointer to select the range of cells. Filling in a cell reference using a named_range or even using other shortcuts to select the range does not give any output.

Is there a better way to do this

Upvotes: 2

Views: 849

Answers (2)

jake
jake

Reputation: 1445

This problem is solvable using Pivot Tables. Select the Columns you wish to use as in the figure below Select the Columns to use

Then select insert and click on recommend pivot table.

select insert and then recommend pivot table

Select sum by...

Select sum by

A new sheet will be added showing your grouped and summarised data

sheet added showing grouped and summarised data

I hope this solves your problem :-)

Upvotes: 0

Andy G
Andy G

Reputation: 19367

Add this formula as an extra column (with a heading), changing the cell-ranges as appropriate:

=SUMIF($A$2:$A$6,A2,$D$2:$D$6)

If your data-table is complete (no blanks cells in column D) or you are using Excel 2010, you can type this formula once and double-click the Fill Handle (the little dot in the bottom-right corner) and it will fill/copy all the way down.

Copy and Paste-Special, Values this column. The data will already be selected, or you can click in the first data-cell and press Ctrl-Shift-Down. Then use the Data tab, Remove Duplicates. Tick every column except the amount one. Then delete the original amount column, replacing it with the new data from your added column.

Upvotes: 1

Related Questions