software

Have created a pivot table to help analyse a 3000-record worksheet. On of the
columns I wish to use icontains year dates. The pivot table has duplicated a
third of dates, thus rendering analysis tricky. Have gone back to source file
amp; rechecked all formatting; any ideas?

To illustrate what I mean, here is a selection from the offending fields:1997405173941718100
199811016432600250
199959659551474150
2000575553771225256
200135083225604632
200244833381101
2003565964109901327
20049118358813831375
2005260244827359945
19891500
19901500
1992
1993
1994
1995
1996
1998
1999
200030
2001775
2002780
2003750
2004880
2005150
(blank)02969548106
Where the entries after 1989 -1500 appear to be left justified whereas all
those above are right justifed. The source feild has been reformatted 3 times
and is text based.

Please help!Changing the format of the cell(s) doesn't change the value.

You'll have to change the values to text (or change the values to numeric).

Changing to numeric is easier.
select an empty cell
edit|copy

Select your range of years
edit|paste special|check add

Now all those text numbers are number numbers and hit the refresh button on the
pivottable.

If you want to change to text, I'd insert a helper column and use:

=a2amp;quot;quot;

drag down and copy|paste special|values over the original range. Then delete
the helper column.

If you still end up with duplicates, watch out for leading/trailing spaces in
your data.

Rob B wrote:
gt;
gt; To illustrate what I mean, here is a selection from the offending fields:
gt;
gt; 1997 4051 7394 1718 100
gt; 1998 1101 6432 600 250
gt; 1999 5965 9551 474 150
gt; 2000 5755 5377 1225 256
gt; 2001 3508 3225 604 632
gt; 2002 4483 3381 101
gt; 2003 5659 6410 990 1327
gt; 2004 9118 3588 1383 1375
gt; 2005 26024 4827 359 945
gt; 1989 1500
gt; 1990 1500
gt; 1992
gt; 1993
gt; 1994
gt; 1995
gt; 1996
gt; 1998
gt; 1999
gt; 2000 30
gt; 2001 775
gt; 2002 780
gt; 2003 750
gt; 2004 880
gt; 2005 150
gt; (blank) 0 2969 548 106
gt; Where the entries after 1989 -1500 appear to be left justified whereas all
gt; those above are right justifed. The source feild has been reformatted 3 times
gt; and is text based.
gt;
gt; Please help!

--

Dave Peterson

Dave, Many thanks for that. THe years just faded away! However, I have a
similar probelm with the month column:

January2036
February2531
March3611
May7211
June16183
July15364
August21241
September11588
October7469
November2825
December6072
20
April4021
Annual10865
August 20
Autumn70
June 6

I tried using this below, but perhaps didn't understand the formula. could
you expnad on how to do this please?

gt; If you want to change to text, I'd insert a helper column and use:
gt;
gt; =a2amp;quot;quot;
gt;
gt; drag down and copy|paste special|values over the original range. Then delete
gt; the helper column.
gt;
gt; If you still end up with duplicates, watch out for leading/trailing spaces in
gt; your data.

Many thanks, Rob.

I'd start by looking for leading/trailing spaces.

And do you really have text in all those cells--or could they be dates just
formatted to show the month?

Rob B wrote:
gt;
gt; Dave, Many thanks for that. THe years just faded away! However, I have a
gt; similar probelm with the month column:
gt;
gt; January 2036
gt; February 2531
gt; March 3611
gt; May 7211
gt; June 16183
gt; July 15364
gt; August 21241
gt; September 11588
gt; October 7469
gt; November 2825
gt; December 6072
gt; 20
gt; April 4021
gt; Annual 10865
gt; August 20
gt; Autumn 70
gt; June 6
gt;
gt; I tried using this below, but perhaps didn't understand the formula. could
gt; you expnad on how to do this please?
gt;
gt; gt; If you want to change to text, I'd insert a helper column and use:
gt; gt;
gt; gt; =a2amp;quot;quot;
gt; gt;
gt; gt; drag down and copy|paste special|values over the original range. Then delete
gt; gt; the helper column.
gt; gt;
gt; gt; If you still end up with duplicates, watch out for leading/trailing spaces in
gt; gt; your data.
gt;
gt; Many thanks, Rob.

--

Dave Peterson