Page 156 - Lean six sigma demystified

Chapter 4 e xC e L Power Too LS for Lean Six Sigm a 135

repair personnel routinely attempt to capture customer complaints, catego-
rize them, and include remarks about the customer’s dilemma. Unfortu-
nately, the categories in most information systems are predefined, inflexible,
and rarely speak to the true nature of the customer’s complaint. And often
the customer, who has waited in a call queue for several minutes, has had
time to think up several questions they need answered, not just one.
In these situations, the information needed to analyze these customer inter-
actions is in the freeform remarks, not in the convenient categories. The infor-
mation captured in the remarks invariably will be more accurate than the
predefined categories. How do we analyze this wild potpourri of short phrases
and abbreviations? The answer lies in Microsoft Excel.

Importing Text with Microsoft Excel

To analyze text with Excel, you must first import the data into Excel. To do
this, you will need to export the customer account and remarks information
from the trouble-reporting system into your PC or local area network.
To simplify deeper analysis, it will be useful to have something about
the customer’s account included with the remark. In a phone company, for
example, having the customer’s phone number will enable further analysis
by digging into the customer’s records.
Then go to Excel and choose File–Open, select Files of Type-All Files, and
open the text file. Excel’s import wizard will then guide you through importing
the data. Text data can either be delimited, which means it contains tab, comma,
or other characters that separate fields, or fixed width, which means the data is
of a consistent length.
The maximum number of characters Excel will store in a cell is 255, so lon-

ger text fields should be edited to fit. More than one cell can be used to store
an entire remark or comment. Excel will allow up to about 65,000 rows to be
imported per Excel worksheet.

Analyzing Text with Excel

Searching the imported text file couldn’t be easier. In the QI Macros,
select the cells filled with text and choose Data Transformation-Word
Count, and the QI Macros will parse the sentences into one and two word
phrases, then pivot them to create a list of the most common words or
phrases. This often identifies the most common issue even when service