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
& rechecked all formatting; any ideas?

To illustrate what I mean, here is a selection from the offending fields:


1997 4051 7394 1718 100
1998 1101 6432 600 250
1999 5965 9551 474 150
2000 5755 5377 1225 256
2001 3508 3225 604 632
2002 4483 3381 101
2003 5659 6410 990 1327
2004 9118 3588 1383 1375
2005 26024 4827 359 945
1989 1500
1990 1500
1992
1993
1994
1995
1996
1998
1999
2000 30
2001 775
2002 780
2003 750
2004 880
2005 150
(blank) 0 2969 548 106
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:

=a2&""

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:

To illustrate what I mean, here is a selection from the offending fields:

1997 4051 7394 1718 100
1998 1101 6432 600 250
1999 5965 9551 474 150
2000 5755 5377 1225 256
2001 3508 3225 604 632
2002 4483 3381 101
2003 5659 6410 990 1327
2004 9118 3588 1383 1375
2005 26024 4827 359 945
1989 1500
1990 1500
1992
1993
1994
1995
1996
1998
1999
2000 30
2001 775
2002 780
2003 750
2004 880
2005 150
(blank) 0 2969 548 106
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!

--

Dave Peterson

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

January 2036
February 2531
March 3611
May 7211
June 16183
July 15364
August 21241
September 11588
October 7469
November 2825
December 6072
20
April 4021
Annual 10865
August 20
Autumn 70
June 6

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

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

=a2&""

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.

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:

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

January 2036
February 2531
March 3611
May 7211
June 16183
July 15364
August 21241
September 11588
October 7469
November 2825
December 6072
20
April 4021
Annual 10865
August 20
Autumn 70
June 6

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

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

=a2&""

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.

Many thanks, Rob.

--

Dave Peterson