Creating summary table from detail table

[RzB]

Using Win XP and Excel 2002

This is a simplification of a problem that I'm trying to solve...

I have a Detail Table as shown in ...

www.gillandroy.com/odds/excel/summarytable

from this table I want to create a Summary Table - also shown
in the above link.

The Detail Table is actually created line by line from some
Access VBA and can contain a variable numbers of lines.
There may be a varying number of distinct instances of text in
the first column, hence a varying number of lines in the Summary
Table.

I was hoping to use the internal power of Excel to create the
Summary Table. Is this going to be possible or am I going to
have to do this from the Access VBA?

Many thanks,
Roy

 

[Guest]

Try something like this:

With your posted detail table in cells A1:C8
and column headings in A1:C1 (Detail, FirstCol, SecondCol

From the Excel main menu:
<data><pivot table>
Check: Microsoft Excel List......Click [Next]
Select A1:C8......Click [Next]
Click the [Layout] button

ROW:
Drag the Detail field here

DATA:
Drag the FirstCol field here and Dbl-click it
Click the [Options] button
Show data as: % of Column
Click the [OK] button

Drag the SecondCol field here and Dbl-click it
Click the [Options] button
Show data as: % of Column
Click the [OK] button to close that window
Clcik the [OK] button to return to the Step 3 window

Select the destination of the pivot table and click the [Finish] button

On the pivot table,
drag the Data field on top of the Total field and release it.

There's your table, as pictured in your posted link

After you update the data, right-click on the pivot table and click Refresh.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Try something like this:

With your posted detail table in cells A1:C8
and column headings in A1:C1 (Detail, FirstCol, SecondCol

From the Excel main menu:
<data><pivot table>
Check: Microsoft Excel List......Click [Next]
Select A1:C8......Click [Next]
Click the [Layout] button

ROW:
Drag the Detail field here

DATA:
Drag the FirstCol field here and Dbl-click it
Click the [Options] button
Show data as: % of Column
Click the [OK] button

Drag the SecondCol field here and Dbl-click it
Click the [Options] button
Show data as: % of Column
Click the [OK] button to close that window
Clcik the [OK] button to return to the Step 3 window

Select the destination of the pivot table and click the [Finish] button

On the pivot table,
drag the Data field on top of the Total field and release it.

There's your table, as pictured in your posted link

After you update the data, right-click on the pivot table and click Refresh.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[RzB]

Ron,
Many thanks for your help. I have actually never used
Pivot tables - it was an interesting introduction.
I was able to work with what you described and have
managed to create what I needed.
Many thanks for the pointers,
Roy

 

[RzB]

Ron,
Many thanks for your help. I have actually never used
Pivot tables - it was an interesting introduction.
I was able to work with what you described and have
managed to create what I needed.
Many thanks for the pointers,
Roy