| (revised 16.05.2009) |
| Preparations
|
|
|
The macro uses data on worksheet 'StrData' and worksheet 'Guidance'.
They must be in the same workbook as the macro.
Without these two worksheets the macro will not work.
It is important that you put the data in the columns of 'StrData' as follows:
|
| - |
In column "A": the strings,
including single characters,
that should be used as row titles in the Pivot Table. |
 |
| Picture 1: Example of classified strings |
|
 |
| Picture 2:
Message when the macro starts running |
|
 |
| Picture 3: A String Pivot Table
|
|
| - |
In column "B": the strings that should be used as column titles in the pivot table
[resulting from running the macro].
|
| - |
In column "C": the words and other strings - e.g. sentences -
that should be placed in the cells of the Pivot table.
|
| - |
The data in columns "A", "B", and "C" must be character.
Other kind of data will result in an run error.
|
|
| Running the Macro
|
| - |
The easist way to run the macro is pressing simultaneously Crl + Shift + 'p'.
You can run the macro from Excel menu [1] as well.
At the beginning of the run the macro shows instructions such as they appear
in certain cells on 'Guidance' worksheet (area A3:A7) (picture 2).
Macro will stop running if it cannot find 'Guidance'.
|
| - |
In seconds a string pivot table (see picture 3) will be written
on a new worksheet. A message follows: 'Pivot table done'.
|
| - |
The pivot table is modified to fit on a screen of 1024 x 768 pixels.
The text is wrapped to take minimal space.
There are border lines between and around the cell of the pivot table.
|
|
| Parameters
|
|
|
As a default the maximum count of rows and columns in the Pivot table is 1000.
You can change the count by entering another count in the cell 'A10' of worksheet 'Guidance'.
|
|
|
The default string separator is '; ' i.e. a semicolon and a space after it.
You can change the separator by entering another string in stead of it in the cell 'A13' of 'Guidance'.
|
|
| Improved version
|
|
|
There is a more sophisticated version of the macro ['StrPiv'] introduced on the
next page.
|
|
[1] The macro can alsoo be run from the Excel 2000's menu by selecting:
"Tools" - "Macro" - "Macros" -"StringPivotTable".
In this case macros 'z_Guidance', 'z_Classify', 'z_Initialize' and 'z_WriteResults'
will in the list of available macros for run.
The 'z_' in front of the names indicates that the macros serve as sub routines of 'StringPivotTable'.
|