Sometimes, data in an Excel file is not structured
ideally to be imported by Prism. Occasionally,
the data is presented in a Pivot-like manner that is more readable and takes up
less storage space. A good example for this
is marketing survey data that often comes in this form:
Image 1 - Marketing Survey Data Sample
Each row represents the answers of a single
survey respondent. The first respondent
- John, gave the answer ‘Good' to the first question, the answer ‘Best' to the
second question, etc.
A typical report created over marketing survey
data such as this is shown in the following pivot image. For each question the numbers of total respondents
as well as the number who answered a particular answer.
Image 2 - Marketing Survey Report
If you try to import that source data as shown
in Image 1, you'll encounter some difficulties creating the report in Image
2. Since each question description comes
in a different FIELD in Excel you will not be able to place them as separate
rows in the pivot. You will be able to
perform interesting analysis, but not quite what we were looking for here.
Restructuring
the Excel Data
In order to create the pivot we want, we need
to the data structured in a flat manner as presented in Image 3:
Image 3 -Flat Marketing Survey data
This format allows you to maximize the
information you can obtain out of data with Prism. It lets you correlate between practically any
member and any other. However, manually
converting our source data to this format can be quite a test on one's
patience. Luckily, you don't have to.
The
Reformatting Macro
We've created an Excel macro (written in VB
Script) that automatically converts data from a pivot-like format (Image 1) to
a flat format (Image 3). It accepts 5
parameters:
- Row Fields Range: The range containing the row
descriptive members. In Image 1, this
would be A2:C5.
- Column Fields Range: The range containing the
column descriptive members. In Image 1,
this would be H1:F1.
- The name of the sheet the original data is in.
- The name of the sheet to contain the
restructured data.
- The maximum number of rows to use on a single
sheet (Excel 2003 is limited to 65K rows per sheet).
To use the macro, download the Excel file here. Once it opens, make sure you enable macros in
Excel or this won't work. The sheet
named ‘Configuration' contains a range for you to type in the parameters and a button
to begin the process. To use this macro
on your data, just create a new sheet, place your data on the top left, point
the macro to this data in the Configuration sheet and click ‘Reformat'. Now create a data source out of the reformatted
data and you're ready to go. Here's an example PSM file.
Note: The flat format takes up more rows than a
pivot-like format. That is why this
macro will automatically break the flat data into as many sheets as required to
accommodate it. Prism allows you to
append data from different sheets so your data will be fully imported.
