Hello Teamministry,
The macro (at the end of the answer) should do what you have asked
for. I tested it with the following data on the first sheet:
Category Draw 1 Draw 2 Draw 3
Foundation 10
Kitchen 100
Roof 20 20
Flooring 20
Windows 100
And produced the following result on the second [previously empty] sheet.
Draw 1
Foundation 10
Flooring 20
Windows 100
Draw 2
Roof 20
Draw 3
Kitchen 100
Roof 20
Other than slight formatting differences, this appears to be the
result you asked for.
The titles of each summary region was taken from the first row of each
column in the original data. The names for each data row was taken
from the first column of each row in the original data.
You should be able to add it to an existing data spreadsheet with the
following steps (the commands may vary slightly on your system - make
a clarification request if you have problems following these steps):
[1] Open the data spreadsheet
[2] Use the menu
Tools -> Macro -> Visual Basic Editor
to start the Visual Basic Editor (VBE).
[3] In VBE, use the menu
Insert Module
An empty window titled something like "teamministry.xls - Module 1
(code)" should appear.
[4] Copy / paste the macro (at the end of the answer) into this window.
[5] May any changes necessary. I have commented in a few places where
a change may be needed. For example, I assume the data is on the first
sheet and the summary goes on the second sheet. If this is not true,
change the lines
CS = 1
SS = 2
to the right sheet numbers.
[6] In VBE, use the menu
Close and return to Microsoft Excel
to return to the excel spreadsheet.
[7] Make sure the summary sheet is empty.
[8] Select a cell in the data region of the data sheet. Ensure there
are no blank rows or columns in the data to be processed. Run the
macro using the menu
Tools -> Macro -> Macros...
and select Summarize from the list and then Run.
The macro should run without any further interaction. When the "busy
cursor" goes away (an hourglass on my system), the macro is complete.
Note that the macro will be saved with the worksheet. Subsequent
attempts to open that worksheet should cause Excel to prompt you with
an "are you sure" type message which you should respond with "Enable
Macros". If the macro is disabled, when the spreadsheet is loaded, you
cannot run it.
If you have any problems using this macro or the instructions are
unclear to you, please make a clarification request.
--Maniac
--- source code of Summarize macro follows ---
Sub Summarize()
'
' Summarize Macro
' Macro created on 1/10/2005 by Maniac
' Extract and summarize values in separate sheet
'
Dim S As Object
' Assume data on first sheet, and summary on second sheet
' Revise here if change needed
CS = 1
SS = 2
Set S = Selection.CurrentRegion
sRows = S.Rows.Count - 1
sCol = S.Columns.Count - 1
'
R = 1 ' row of summary sheet active
For I = 1 To sCol
' copy title for next summary region
SV$ = S.Cells(1, I + 1).Value
Sheets(SS).Select
Cells(R, 1).Value = SV$
R = R + 1
Sheets(CS).Select
For J = 1 To sRows
V = S.Cells(J + 1, I + 1).Value
If (V <> 0) Then
C$ = S.Cells(J + 1, 1).Value
Sheets(SS).Select
Cells(R, 1).Value = C$
Cells(R, 2).Value = V
R = R + 1
Sheets(CS).Select
End If
Next J
R = R + 1
Next I
End Sub |
