Excel 2000 merge cells

  • Thread starter Thread starter Kaygee
  • Start date Start date
K

Kaygee

I have several thousand rows of customer information such
as AR info, time spent, # of people within the company,
etc. However, some companies appear more than once. I want
to merge the rows where the companies appear twice and add
up the columns where there is a number. Where there is
text, I don't care what happens to the text.

Example

Company Name Contract Time Spent # of people
AAAA New 300 10
AAAA Addendum 100 5
BBBB New 250 6
BBBB Addendum 120 3

I want two rows returned after using a macro or whatever
that have just the company names, either new or addendum
for Contract, 400 and 370 for time spent and 15 and 9 for
# of people.
 
Actually, I want to merge the rows so that there are no
more duplicates and what's left is a sum of what was in
the cells. In my example, I started with 4 rows, but want
to end up with 2.
 
Select your range and do Data|Pivottable.

Follow the wizard until you get to a dialog with a Layout button on it.
Click that button.

Drag the company name "button" into the Row field.
Drag the time spent box into the data field
drag the # of people box into the data field

if either time spent or # of people don't show "Sum of", click on it and select
Sum.

Then continue with the wizard (ok/yes/finish the way out)

Now grab that grey Data Button and drag it one cell to the right and let go.

Pretty neat, huh?

If you're interested in pivottables, here are some references:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
A B C D E F G H
1 CoName Contract Time People CoName Time People
2 AAAA New 300 10 AAAA 400 15
3 AAAA Addendum 100 5 BBBB 370 9
4 BBBB New 250 6
5 BBBB Addendum 120 3

Enter Formula =SUMIF($A:$A,$F2,C:C) into Cell G2 and copy it Cell H2. Then drag-copy Range G2:H2 downward.
 
Back
Top