Eliminate duplicate entries but keep running total?

[9tanstaafl9]

Basically I have a simple set of data sorted by cost code (col B). Each cost code has an amount (col C). There may be one or more of each cost code.

When all is said and done, what I want is to see only one of each cost code. If more than one had originally existed, I want the total to be the sum of all of them. NO EXTRA ROWS.

I can't for the life of me figure out how to do this. I know there is probably a simple way, but the search terms are too general and I get info on creating subtotals that doesn't help.

If anyone could help me, or point me in the right direction I would appreciate it immensely. I can figure things out if you tell me where to look, my issue is that I have no idea where to look.

Thanks!!!!!!!

 

[RickXL]

Hi,

How automatic does it need to be?

For instance, you can make a copy of the original data;
Copy it to another sheet;
Remove duplicates (Tab: Data, Section: Data Tools, Remove Duplicates)
Then use the data on both sheets to get the totals.

Excel 2013
	B	C
1	cost code	cost
2	1	151
3	1	173
4	1	117
5	2	195
6	2	180
7	2	169
8	3	190
9	3	122
10	4	180
11	5	160
12	6	187
13	7	138
14	7	135
15	8	133
16	9	156
17	9	119
Sheet1

Excel 2013
	B	C	D
1	cost code	cost	total
2	1	151	441
3	2	195	544
4	3	190	312
5	4	180	180
6	5	160	160
7	6	187	187
8	7	138	273
9	8	133	133
10	9	156	275
Sheet2

Cell Formulas
Range	Formula
D2	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B2,Sheet1!$C$2:$C$17)
D3	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B3,Sheet1!$C$2:$C$17)
D4	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B4,Sheet1!$C$2:$C$17)
D5	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B5,Sheet1!$C$2:$C$17)
D6	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B6,Sheet1!$C$2:$C$17)
D7	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B7,Sheet1!$C$2:$C$17)
D8	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B8,Sheet1!$C$2:$C$17)
D9	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B9,Sheet1!$C$2:$C$17)
D10	=SUMIF(Sheet1!$B$2:$B$17,Sheet2!B10,Sheet1!$C$2:$C$17)

 

[9tanstaafl9]

THANK YOU! I knew there must be an easy way to do this, I just couldn't think clearly. Also, I didn't know there was a remove duplicates command. Not sure how I missed that, but it just never came up before.

I really appreciate your help!

 

[jasonb75]

Or you could create a pivottable from the original data using the cost codes as row headers.