Vlookup or Sum formula; Please help. thank you; Vera

[Guest]

I have in A1 to A10 the following names.
A1 Scott
A2 Joan
A3 Bob
A4 Mike
A5 Betty
A6 Paul
A7 Joan
A8 Mike
A9 Kathy
A10 Scott

Names can appear randomly more than once.
In col B1:B10 numbers

B1 50
B2 2
B3 44
B4 19
B5 12
B6 77
B7 18
B8 11
B9 5
B10 10

I need a formula to get in col D1 and down every name (just once for those that have more occurrences).

D1 Scott
D2 Joan
D3 Bob
D4 Mike
D5 Betty
D6 Paul
D7 Kathy

In col E1 near every name I need the total for that name from col B.

E1 60
E2 20
E3 44
E4 30
E5 12
E6 77
E7 5

Thank you for any help.

Vera

 

[Aladin Akyurek]

Vera,

Insert a row before your data.

Your data is now in A2:B11.

In A1:B1 enter the labels "Name" and "Amount", respectively.

Activate A2.
Activate Data|Filter|Advanced Filter.
Check 'Copy to another location'.
Enter for 'List range' $A$1:$A$11.
Enter for 'Copy to' $D$1.
Check 'Unique records only'.
Click OK.

In E2 enter and copy down as far as needed:

=SUMIF($A$2:$A$11,D2,$B$2:$B$11)

Aladin

On 2002-03-14 06:22, Anonymous wrote:
I have in A1 to A10 the following names.
A1 Scott
A2 Joan
A3 Bob
A4 Mike
A5 Betty
A6 Paul
A7 Joan
A8 Mike
A9 Kathy
A10 Scott

Names can appear randomly more than once.
In col B1:B10 numbers

B1 50
B2 2
B3 44
B4 19
B5 12
B6 77
B7 18
B8 11
B9 5
B10 10

I need a formula to get in col D1 and down every name (just once for those that have more occurrences).

D1 Scott
D2 Joan
D3 Bob
D4 Mike
D5 Betty
D6 Paul
D7 Kathy

In col E1 near every name I need the total for that name from col B.

E1 60
E2 20
E3 44
E4 30
E5 12
E6 77
E7 5

Thank you for any help.

Vera