MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel - Data structuring

  Asked By: Darcy    Date: Dec 14    Category: MS Office    Views: 1185

can you guys help me out with this problem.
I have an excel file that has data from Columns A through S. But the
columms that are of importance for this exercise will be A,B and C.
Column A contains the "Primary Branch Code". The format of data in
this column is a 3-digit text that is the code. Column B contains
secondary branch code. The format is again a three digit code. But
there are several secondary branch codes for a given primary code.
Column C contains a set of metrics. The format of this is just a sentence.

The problem is that the data is not structured. Let me describe how it
is currently and then also describe how do I want that to be.

Current format:

Column A: Column B Column c
CHS CHQ,BIE, DIE,DRV, ... n terms customer Service Time1
CHS CHQ,BIE, DIE,DRV, ... n terms customer Service Time2
CHS CHQ,BIE, DIE,DRV, ... n terms customer retention ratio
CHS CHQ,BIE, DIE,DRV, ... n terms customer response time
ABC CHQ,BIE, DIE,DRV, ... m terms customer Service Time
ABC CHQ,BIE, DIE,DRV, ... m terms customer response time

Desired format

Column A: Column B Column c
CHS CHQ customer Service Time1
CHS CHQ customer Service Time2
CHS CHQ customer retention ratio
CHS CHQ customer response time
CHS BIE customer Service Time1
CHS BIE customer Service Time2
CHS BIE customer response Time
CHS BIE customer service time

Like wise it goes on for all primary branch codes and secondary branch
codes. In essence what i desire is each row should have a primary
branch code, secondary branch code, and metrics. First Column C should
be allowed to vary. Then Column B. Then Column C. The metrics are not
all same. Similarly the secondary branch codes are not the same for
all primary branch codes. This is illustrated in the above table.

Can you help me out please?



1 Answer Found

Answer #1    Answered By: Birke Fischer     Answered On: Dec 14

There are two separate parts to this, I think: (1) create the rows, (2) sort
them into the desired order.

I suggest you keep the steps separate and just use Excel's normal sorting
capability for step (2).

Now, step (1) is simply a task of breaking up each column  B cell and
producing one row  for each of its parts. The easiest way to separate a
comma-separated list is with the Split function which will create an array
containing the various entries. You also have a space after the comma after
BIE, so you'll presumably need to use the Trim function to remove unwanted

Didn't find what you were looking for? Find more on Excel - Data structuring Or get search suggestion and latest updates.