combining duplicate rows

[jezzica85]

Hi all,
Does anyone know if there's a way to turn something like this:
I M R SUM
2 7 1 10
a 2 2
a 3 3
b 1 1
c 4 4

into this:

I M R SUM
2 7 1 10
a 2 3 0 5
b 0 0 1 1
c 0 4 0 4

I, M, R are column headers, and a, b, and c are data. Just below the column
headers is the sum for all the rest of the values in the column. I'm not
very good at macros, so if the solution is a macro would you mind telling me
how to implement it?
Thanks so much!

[Dave Peterson]

If your data is nicely sorted by column A, you could use:

Data|subtotals and sum each column based on the change in column A.

After you do that, you'll see outlining symbols to the left of the worksheet.
You can use those to hide/show the details.

Another option is to learn a bit about pivottables.
(add headers to any column that doesn't have one)

Select your data (headerrow through bottom right cell)
Data|pivototable
follow the wizard until you get to a step that has a Layout button on it.
Hit that layout button
drag the heaeder for the category to the row field
drag the header for I to the data field
(Double click on that and make sure it says "Sum of" instead of "count of")

do the same with the other column headers

Finish up that wizard.

Now drag the grey cell with Data in it directly to its right and let go.
(right on top of the cell with Total in it)

Now right click anywhere in that pivottable and choose "table options"
Make sure that "for empty cells, show" box is checked and choose 0 as the
character to show (if you really want to see 0's.)


jezzica85 wrote:
>
> Hi all,
> Does anyone know if there's a way to turn something like this:
> I M R SUM
> 2 7 1 10
> a 2 2
> a 3 3
> b 1 1
> c 4 4
>
> into this:
>
> I M R SUM
> 2 7 1 10
> a 2 3 0 5
> b 0 0 1 1
> c 0 4 0 4
>
> I, M, R are column headers, and a, b, and c are data. Just below the column
> headers is the sum for all the rest of the values in the column. I'm not
> very good at macros, so if the solution is a macro would you mind telling me
> how to implement it?
> Thanks so much!

--

Dave Peterson