How to combine multiple dollar amounts by an id number that may appear multiple times?

[Antonia]

Hi,

I have amounts in a spreadsheet that I need to change from individual amounts broken out by id numbers to consolidated amounts per id number. (Tried to attach spreadsheet--not sure it worked. Hopefully example below is clear). Can someone give me the easiest way to change the info as simply and quickly as possible?

*FROM Separate Amounts by EEID
0001326 138.14
0001473 95.46
0001600 4,389.58
0001600 2,194.92
0001600 2,194.80
0001692 719.87
0001692 8,638.41
0001692 2,159.65
0001692 2,159.60
0001996 4,162.20
0001996 2,081.23
0001996 2,081.14

*TO Consolidated Amount by EEID
0001326 138.14
0001473 95.46
0001600 8,779.30
0001692 13,677.53
0001996 8,324.57

There are thousands of cells in the id number column, so I can't do it manually.

Thank you!

Antonia

[FDibbins]

If you already have the unique ID's...
=SUMIF($B$2:$B$13,E2,$C$2:$C$13)
copied down

[Tony Valko]

Create a list of the unique IDs.

You can do this easily by using advanced filter...

http://contextures.com/xladvfilter01.html#FilterUR

Then, you can use a SUMIF function like this entered in F2 and copied down:

=SUMIF(B:B,E2,C:C)

[etaf]

to extract the unique IDs
enter and array formula

=IF(OR(INDEX($B$2:$B$100, MATCH(0, COUNTIF($I1:I$1, $B$2:$B$100), 0))=0,ISERROR(INDEX($B$2:$B$100, MATCH(0, COUNTIF($I1:I$1, $B$2:$B$100), 0)))),"",INDEX($B$2:$B$100, MATCH(0, COUNTIF($I1:I$1, $B$2:$B$100), 0)))

using control + Shift + Enter

Change $I1:I$1 to the column you put the formula in

and then use a SUMIF
=SUMIF(B:B,I2,C:C)

see attached

[Antonia]

Wow! Thank you so much James for not only helping me with the list and the consolidating amounts but also attaching an example. You're the only one who did that. And, that's exactly what I need since I am terribly sleep deprived and under pressure to get this done. Unfortunately, I tried copying your formula to create the unique ID list, and it didn't work when I copied it down. I also changed the 100 to 13756, which is the row number of the cells in column B?

I am sorry I am such a novice, but could you possible provide any more instruction? I am not sure what to do with the Control Shift Enter either. Thank you very much for your help.

- Antonia

[Antonia]

Sorry--See how sleep deprived, not sure how I got James out of Wayne/Etaf. But I appreciate your help.

- Antonia

[etaf]

is you sample using the exact same columns as your live data ?
if not

what column are the IDs in
what column are the values you want to sum
What column do you want to use for the extract of unique IDs ?
What column do you want to put the Sum in

when you enter the formula to extract the unique IDs
instead of using enter or the tick to confirm the formula
you need to use the Control Key + the shift key + the enter key - all at the same time

then it puts {} around the formula and enters as an array


This assumes you are extracting from column B
this assumes the unique IDs are entered into column I
So you enter this into cell I2 and copy down

=IF(OR(INDEX($B$2:$B$13756, MATCH(0, COUNTIF($I1:I$1, $B$2:$B$13756), 0))=0,ISERROR(INDEX($B$2:$B$13756, MATCH(0, COUNTIF($I1:I$1, $B$2:$B$13756), 0)))),"",INDEX($B$2:$B$13756, MATCH(0, COUNTIF($I1:I$1, $B$2:$B$13756), 0)))

if you wanted to use say column E to extract the IDs

=IF(OR(INDEX($B$2:$B$13756, MATCH(0, COUNTIF($E1:E$1, $B$2:$B$13756), 0))=0,ISERROR(INDEX($B$2:$B$13756, MATCH(0, COUNTIF($E1:E$1, $B$2:$B$13756), 0)))),"",INDEX($B$2:$B$13756, MATCH(0, COUNTIF($E1:E$1, $B$2:$B$13756), 0)))

[Antonia]

Ok. The advanced filter thing worked after I copied the list to a new sheet. Then I used the sumif thing. You guys literally saved my life. Thank you!!!

[Antonia]

Ok. Looks like I spoke too soon. Looks like the advanced filter only pulled out the unique ids that had multiple ids associated with it. Or only some of them. Some of the unique ids didn't make it into the list. Help?!

[Antonia]

Ok. So it dawned on me. The time Wayne and Tony are trying to spend trying to help me manipulate the list. Seems so easy for them, maybe it is easier if I just attach the list and you send it back to me with the fix? I don't know if that violates some sort of excel forum rule. But, I would be so obliged if I just had the list consolidated the way it needs to be. Maybe Wayne, you'd be willing to help me out since you attached that other excel spreadsheet back to me. Thank you for anything and everything you are willing to help with. Now I gotta find the place to attach the spreadsheet.

Antonia

[Antonia]

Ok. So it dawned on me. The time Wayne and Tony are trying to spend trying to help me manipulate the list. Seems so easy for them, maybe it is easier if I just attach the list and you send it back to me with the fix? I don't know if that violates some sort of excel forum rule. But, I would be so obliged if I just had the list consolidated the way it needs to be. Maybe Wayne, you'd be willing to help me out since you attached that other excel spreadsheet back to me. Thank you for anything and everything you are willing to help with. Now I gotta find the place to attach the spreadsheet.

Antonia

[Tony Valko]

The advanced filter works OK on your sample file.

[etaf]

because of the size of the data - the array is going to do a lot of calculating

A pivot table will work much better

[Antonia]

absolute rock star. thank you!

[etaf]

you are welcome, thanks for a rep