New to Pivot Tables - problem data

[Torri]

The Data I am pulling from is:<o></o>
<o> </o>
[TABLE="class: MsoNormalTable, width: 193"]
<tbody>[TR]
[TD="width: 78"]

ATA<o></o>​

[/TD]
[TD="width: 84"]

Claim Amount<o></o>​

[/TD]
[TD="width: 96"]

# Units<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

11/7/2010<o></o>​

[/TD]
[TD="width: 84"]

$322.89<o></o>​

[/TD]
[TD="width: 96"]

4<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

3/29/2011<o></o>​

[/TD]
[TD="width: 84"]

$41.36<o></o>​

[/TD]
[TD="width: 96"]

24 (4 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

3/4/2011<o></o>​

[/TD]
[TD="width: 84"]

$20.68<o></o>​

[/TD]
[TD="width: 96"]

12 (2 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

3/24/2011<o></o>​

[/TD]
[TD="width: 84"]

$72.38<o></o>​

[/TD]
[TD="width: 96"]

42 (7 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

7/29/2011<o></o>​

[/TD]
[TD="width: 84"]

$50.79<o></o>​

[/TD]
[TD="width: 96"]

2<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

8/4/2011<o></o>​

[/TD]
[TD="width: 84"]

$101.58<o></o>​

[/TD]
[TD="width: 96"]

4<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

8/5/2011<o></o>​

[/TD]
[TD="width: 84"]

$253.96<o></o>​

[/TD]
[TD="width: 96"]

10 (2 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

12/9/2011<o></o>​

[/TD]
[TD="width: 84"]

$76.19<o></o>​

[/TD]
[TD="width: 96"]

3 units <o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

12/15/2011<o></o>​

[/TD]
[TD="width: 84"]

$78.36<o></o>​

[/TD]
[TD="width: 96"]

4 units<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

9/13/2011<o></o>​

[/TD]
[TD="width: 84"]

$54.42<o></o>​

[/TD]
[TD="width: 96"]

2<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

12/13/2011<o></o>​

[/TD]
[TD="width: 84"]

$612.23<o></o>​

[/TD]
[TD="width: 96"]

25<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

10/27/2011<o></o>​

[/TD]
[TD="width: 84"]

$24.49<o></o>​

[/TD]
[TD="width: 96"]

1<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

3/10/2012<o></o>​

[/TD]
[TD="width: 84"]

$24.49<o></o>​

[/TD]
[TD="width: 96"]

1<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

4/18/2012<o></o>​

[/TD]
[TD="width: 84"]

$489.78<o></o>​

[/TD]
[TD="width: 96"]

20 (4 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

4/16/2012<o></o>​

[/TD]
[TD="width: 84"]

$124.08<o></o>​

[/TD]
[TD="width: 96"]

72 (12 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

5/8/2012<o></o>​

[/TD]
[TD="width: 84"]

$1,224.45 <o></o>​

[/TD]
[TD="width: 96"]

50 (10 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

7/1/2012<o></o>​

[/TD]
[TD="width: 84"]

$59.86 <o></o>​

[/TD]
[TD="width: 96"]

1<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

7/15/2012<o></o>​

[/TD]
[TD="width: 84"]

$489.78 <o></o>​

[/TD]
[TD="width: 96"]

20 (4 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

7/17/2012<o></o>​

[/TD]
[TD="width: 84"]

$122.45 <o></o>​

[/TD]
[TD="width: 96"]

5 (1 ctn)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

11/10/2012<o></o>​

[/TD]
[TD="width: 84"]

$119.72 <o></o>​

[/TD]
[TD="width: 96"]

2 (1ctn)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

11/18/2012<o></o>​

[/TD]
[TD="width: 84"]

$199.54 <o></o>​

[/TD]
[TD="width: 96"]

10 (2 ctns)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

10/29/2012<o></o>​

[/TD]
[TD="width: 84"]

$748.82 <o></o>​

[/TD]
[TD="width: 96"]

192 (32 ctn)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

12/6/2012<o></o>​

[/TD]
[TD="width: 84"]

$25.58<o></o>​

[/TD]
[TD="width: 96"]

1<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

12/9/2012<o></o>​

[/TD]
[TD="width: 84"]

$25.58<o></o>​

[/TD]
[TD="width: 96"]

6<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

12/3/2012<o></o>​

[/TD]
[TD="width: 84"]

$126.98<o></o>​

[/TD]
[TD="width: 96"]

5<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

11/4/2012<o></o>​

[/TD]
[TD="width: 84"]

$38.09<o></o>​

[/TD]
[TD="width: 96"]

3<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

7/31/2012<o></o>​

[/TD]
[TD="width: 84"]

$122.45<o></o>​

[/TD]
[TD="width: 96"]

5<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

2/17/2013<o></o>​

[/TD]
[TD="width: 84"]

$54.42<o></o>​

[/TD]
[TD="width: 96"]

5<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

10/26/2012<o></o>​

[/TD]
[TD="width: 84"]

$20.68<o></o>​

[/TD]
[TD="width: 96"]

12<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

3/23/2013<o></o>​

[/TD]
[TD="width: 84"]

$982.83<o></o>​

[/TD]
[TD="width: 96"]

252<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

4/8/2013<o></o>​

[/TD]
[TD="width: 84"]

$51.15<o></o>​

[/TD]
[TD="width: 96"]

12<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

4/3/2013<o></o>​

[/TD]
[TD="width: 84"]

$39.91<o></o>​

[/TD]
[TD="width: 96"]

2<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

2/25/2013<o></o>​

[/TD]
[TD="width: 84"]

$54.42<o></o>​

[/TD]
[TD="width: 96"]

5<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

3/27/2013<o></o>​

[/TD]
[TD="width: 84"]

$23.40<o></o>​

[/TD]
[TD="width: 96"]

6<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

5/20/2013<o></o>​

[/TD]
[TD="width: 84"]

$25.58<o></o>​

[/TD]
[TD="width: 96"]

6<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

5/7/2013<o></o>​

[/TD]
[TD="width: 84"]

$49.89<o></o>​

[/TD]
[TD="width: 96"]

5<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 78"]

6/10/2013<o></o>​

[/TD]
[TD="width: 84"]

$25.58<o></o>​

[/TD]
[TD="width: 96"]

6<o></o>​

[/TD]
[/TR]
</tbody>[/TABLE]
<o> </o>
<o></o>I can do basic pivot tables but this has muddled me. I am so new to it all. Please help.

This was my instructions:<o></o>
<o> </o>
I need them placed into graphs showingthe time line from 2010 to present.<o></o>
<o> </o>
We need to measure three areason a monthly bases; frequency of events, total amount of claim for the monthand unit count for the moth. <o></o>
<o> </o>
I need it in a three bar graphand line chart with three spate lines for each measurement.<o></o>

 

[Dan Rittgers]

Before you think pivot table, think clean the data. You need to insert a column in the data and scrub it. Before you build the PV do not forget to give the column a heading.

 

[Torri]

Thanks so much. I finally worked it out and you were correct. Add columns to clean up the data - much more simple that way!

 

[Dan Rittgers]

Thanks so much. I finally worked it out and you were correct. Add columns to clean up the data - much more simple that way!

I use the following formula for data greater than 100 lines

=VALUE(TRIM(MID(A3,1,FIND("(",A3)-3)))