merging duplicate information in a column

[alanf]

I have a spread sheet that has 12 worksheets (January-December). Each sheet has a column for a name and a column for the person's commission. Some names are in all 12 worksheets, some are in some of the worksheets and some are in only one worksheet.

I want to combine all of these worksheets into one list and combine the entries that are in multiple worksheets into one entry with their commission added together.

For example:

January
NAME COMMISSION
john smith $3000
tom brown $1000
jim jones $200

February
NAME COMMISSION
john smith $3500
jim jones $1000

March
NAME COMMISSION
jerry james $500
tom brown $5000

The above example would end up looking like:

NAME COMMISSION
john smith $6500
tom brown $6000
jim jones $1200
jerry james $500

Anyone got any idea of how to do this?

Thanks in advance,

Alan

 

[Mark W.]

See the Excel Help topic for "Consolidate data by category".

 

[alanf]

I really would like to have someone explain how to do it if possible. The help is very unclear.

 

[Mark W.]

On 2002-04-10 10:27, alanf wrote:
I really would like to have someone explain how to do it if possible. The help is very unclear.

It's really quite easy. Let's say that A1:B4 contains...

{"Fruit","Qty"
;"Apples",10
;"Oranges",20
;"Bananas",30}

...and A6:B9 contains...

{"Fruit","Qty"
;"Apples",5
;"Oranges",15
;"Bananas",25}

Select cell A11, choose the Data | Consolidate... menu command, add the references shown above, check both "Top row" and "Left column", and press [ OK ].

A11:B14 now contains...

{"Fruit","Qty"
;"Apples",15
;"Oranges",35
;"Bananas",55}
This message was edited by Mark W. on 2002-04-10 10:35