Excel2007 - Manipulating Raw Data (Pivot Table? VLookup?) 1

[ladyck3]

The Raw data that I am receiving is, shall we say, very raw but...there has GOT to be a better way of doing it than the way I am manually deleting empty cells, now.

Two Sections:
1. Cause/Reason Cells P3:X(last line)
2. Solution Cells Z3:AK(last line)

In Column AM is the resulting cost.

For the Cause/Solution I am repeating this for each section:

Breaking this into two sections with the costs/pricing copied to each section. Then I delete all of the preceding empty cells and the empty cells between the Cause/Solution and the price.

I end up with two lists...
Cause - Price
Solution - Price

I then have a working pie chart and I just enter new data in the existing table but there has GOT to be a better way to show

Quanty of (Examples)
Cause:
Lost 2 instances total cost for "Lost" is $305.23

Solution:
FOC (Free of Charge) Repair 2 instances $35.00


The problem is, it can be 'any' cause and 'any solution' so its all over the place. Is there a way to more easily pull this data? A pivot chart I don't see working because there's no uniformity in the data at all... too many empty cells to get count and sum.

(Skip if you are reading this, I have many things that I have to report on)

Thanks in advance.

LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

Assuming that you have 2 tables in the raw data:

Cause/Reason & Solution

Are the rows in the each table realated, ie if you SORTED the Cause/Reason table (P:X) in some different order, would that 'mess up' the data?

How is column AM (Resulting Cost) related to the tables?

Would sorting either of the tables into a different order affect Resulting Cost?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

This is one large spreadsheet of data....
The section from P to AM I copy into another worksheet but each line has

Cause Solution Price
Cause Solution Price
Cause Solution Price
Cause Solution Price

Etc...

Each line on the spreadsheet is a different record and any one of the following Causes may be selected (each in its own column)

Agent Error
Multiple Repairs
Lost Product
HQ Request
BBB Request
Customer Sat
Legal Issue
Parts Issue
Other


Then any one of the following solutions (on the same line, each in its own column)

FOC Repair
Replacement Unit
New Unit
BTHDST
CHRG
BAT
CS
Pre-Mailer
Upgraded Shipping
BTEH
BCOV
Other

And in AM is the price. You can have any multiple of combinations so the data on the individual lines can look like this:

x | x $200.00
x | x $ 0.79
x | x $ 3.00
x | x $435.62


| = the dividing line between Cause | Solution
See the prediciment?





LadyCK3
aka: Laurie

 

[SkipVought]

You have a REPORT format.

Using a REPORT to analyze data or to use as a data source can be EXTREMELY difficult! You no doubt, have a daunting task.

What are the values represented by your x's in the above example?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

And what does each row represent, since each column is accounted for as a cause/solution value? (9 Causes & 12 Solutions)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Row 1 are the merged cells
First Merged cell spans the 9 columns below and is the "CAUSE" section

Row 2

Agent Error Multiple Repairs Lost Product HQ Request

ETC (Space constraints the rest of this section is listed below)

BBB Request
Customer Sat
Legal Issue
Parts Issue
Other

Next on Row 1 is a large merged cell of 12 columns SOLUTION
and again each of the 12 columns is represented by the headings below.


FOC Repair
Replacement Unit
New Unit
BTHDST
CHRG
BAT
CS
Pre-Mailer
Upgraded Shipping
BTEH
BCOV
Other


The X's can be any of the above depending on the CAUSE / SOLUTION section... ANY combination


===================================================

BBB Request | Pre-Mailer $200.00
Agent Error | BTEH $ 0.79
HQ Request| BAT $ 3.00
Other |New Unit $435.62

Mind you the uneven listing is the sproadic entry on whatever column it belongs in and the headers for each column is exactly what is listed in the cell on whatever row (Record) involved.

An example of a Cause Column can look like this:
Cause
Customer Sat(Header)
1.
2. Customer Sat
3. Customer Sat
4. Customer Sat
5. Customer Sat
6. Customer Sat
7.
8. Customer Sat
9. Customer Sat
10.
11.
12. Customer Sat


In the next section are the Solutions
FOC Repair (Header)
1.
2. FOC Repair
3. FOC Repair
4.
5. FOC Repair
6.
7.
8.
9.
10. FOC Repair
11.
12.

As you can see, the emtpy lines in each of these examples are empty because they pertain to a different Cause or a Different Solution for that record.

What I'm doing is pulling this in a weekly basis right now so the task is not so daunting at the end of the month and manually deleting any blank cells until I have a column of all Causes and the total pricing for each record/ then all 12 colutions and the same pricing for each record.

THEN I run a pivot table to get the total $ value per category for each... its daunting that's for sure.

BTW... Skip you monitor this tool for a living don't you? You must, I can't imagine an employer allowing you to answer everyone's posts like this and so quick and still get 'other' work done.... I do thank you so much for your tutoring



LadyCK3
aka: Laurie

 

[SkipVought]

What column is Customer Sat?

Are there different values in that column?

What does it mean when there is an empty cell in that column?

I understand the FOC Repair type values, but Customer Sat is something new.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I think that we're close to getting you some useable data, if we can nail down the entire sheet structure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

OOPS, I guess I missed Customer SAT.

So here's what to do.

1) In the column directly to the LEFT of each section and the Amount, enter ID in the Heading row and =ROW() in the next row and copy down to the last row of data, in all three columns. BTW you can do column O first and then copy 'n' paste into columns Y and AL.

Now we're going to NORMALIZE your data, to get it all into one useable table, using the PivotTable Wizard.

Use the process outlined in faq68-5287.

Use this process on each section. For instance the first section will include data from O:X, column O being the ID column. So you'll do this THREE TIMES: O:X, Y:AK and AL:AM

When you get those THREE results, post back for the next step.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

It means customer satisfaction... that really is not relevant its just a for the column (headers are on row 2 because

Row 1 is a merged set of cells above... let's negate the header names as they are and do this
Row 2 is the header for each Cause/Solution
Row 3 is a count of the instances

Cause=CAU
Solution=SOL

6 records are shown below

I'm going to narrow this up a bit to fit in this text box:

CAUSE | SOLUTION
======================================================
CAU1 CAU2 CAU3 | SOL1 SOL2 SOL3 SOL4 SOL5 COST
------------------------------------------------------------
3 1 2 | 1 1 0 2 2
------------------------------------------------------------
CAU1 | SOL1 $ 56
CAU1 | SOL2 $ 23
CAU2 | SOL4 $100
CAU3 | SOL4 $150
CAU1 | SOL5 $ 7
CAU3 SOL5 $ 1

So I am calculating
CAU1 (3 instances) $86.00 (/3= avg cost for CAU1)
CAU2 (1 Instance) $100 (AVG CAU2)
CAU3 (2 instances)$151.00 (/2 = avg cost for CAU3)

THEN another pie chart for solutions
SOL1 (1 instance) $56
SOL2 (1 instance) $23
SOL3 (0 instances) $ 0
SOL4 (2 instances) $250 (/2 = AVG Cost)
SOL5 (2 instances) $ 8 (/2 = AVG Cost)

I'm not charting the average cost only the list of Causes
and another for a list of Solutions.

Does this help at all? I hope so

NOTE: I hope this is helpful, do you have ANY CLUE as to how long it took me to get this to appear sorta ok... what do you guys use when you write in this text box and the table drawings turn out ok... gosh this is more work than the project LOL

LadyCK3
aka: Laurie

 

[SkipVought]

Look at my LAST POST.

Your solution starts there.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's a short example of what you would end up with...
[tt]
ID Cause Solution Amt
1 Agent Error BAT 22
3 Lost Product BTHDST 33
2 Multiple Repairs CS 44
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

OK. Here's a much easier solution.

In the heading row in columns AN:AO anter Cause & Solution
in the NEXT row I'm assuming that its'ROW 3...
[tt]
AN3: =INDEX(P3:X3,1,MATCH("*",P3:X3,0))
AO3: =INDEX(Z3:AK3,1,MATCH("*",Z3:AK3,0))
[/tt]
Now AM:AO is something you can do aggregations on, like a PivotTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Using the referenced FAQ, this link does not exist

That's because your data is NOT NORMALIZED. You might want to check out this EXCELLENT link 'Fundamentals Of Relational Database Design'

I don't know how to access the Wizard.. I don't have an add-in for it. I usually go to (Office 2007) the Inset Ribbon and select PivotTable.

am I missing an add-in?

LadyCK3
aka: Laurie

 

[SkipVought]

The link certainly does exist. I just opened the link.

However, my last post gives you a much simpler solution. You could get up and working in less than 5 minutes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

OH MY GOD... Skip, I LOVE YOU... HOURS and HOURS and HOURS of wasted time I will now have available to me for other things.. WOAH I'm Jazzed....

The Index formulas worked like a CHARM!

I wish I could give you a BAZILLION STARS!!

My Pivot tables ROCK!

XL SIZE HUGS (get the XL reference) hehehehehe
I owe you SO MUCH... I don't quite "get" the index function but now that I have had practical use of it (a need for it) I can study what its doing (I mean I know what its doing) but the how and why of the formula itself.

THANK YOU SO VERY MUCH! You are AWESOME!!!

STARS STARS STARS to INFINITY!!


LadyCK3
aka: Laurie

 

[SkipVought]

"to INFINITY!!"

I'm 'Buzed!' ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

You are my EXCEL.....LENT (hahahah)

HEEEEEEEEEEEEEEEEEEEEEE-ROOOOOOOO

http://WWWWWWWWWWWWWWWWWW

LadyCK3
aka: Laurie

 

[SkipVought]

LENT is not here quite yet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}