I have created a data set from several (5) different worksheets with the aim
of using a pivot table to sort and collate the data. This is something I have
done many times before.

In this instance when I create the Pivot Table one section of the data is
read by Excel as if it was a a set of different numbers. So I get a Pivot
Table with 4 sets of data collated correctly and one collated as if the
numbers were different.

The only different operation carried out on this particular data set is that
the (part) numbers contained "-"'s so I have used replace to remove the -
with null.

I cannot see how this data is different yet Excel does!

Any ideas please....

As a guess one set of data is numbers and the other is text... The number 5
is very different from the text string "5". Try to convert them all to number
or text.
--
HTH...

Jim Thomlinson


"Dave S" wrote:

I have created a data set from several (5) different worksheets with the aim
of using a pivot table to sort and collate the data. This is something I have
done many times before.

In this instance when I create the Pivot Table one section of the data is
read by Excel as if it was a a set of different numbers. So I get a Pivot
Table with 4 sets of data collated correctly and one collated as if the
numbers were different.

The only different operation carried out on this particular data set is that
the (part) numbers contained "-"'s so I have used replace to remove the -
with null.

I cannot see how this data is different yet Excel does!

Any ideas please....

Hi Jim

Thanks but I have already tried formatting all the data to the same type;
text and general, but still the same problem.

Dave


"Jim Thomlinson" wrote:

As a guess one set of data is numbers and the other is text... The number 5
is very different from the text string "5". Try to convert them all to number
or text.
--
HTH...

Jim Thomlinson


"Dave S" wrote:

I have created a data set from several (5) different worksheets with the aim
of using a pivot table to sort and collate the data. This is something I have
done many times before.

In this instance when I create the Pivot Table one section of the data is
read by Excel as if it was a a set of different numbers. So I get a Pivot
Table with 4 sets of data collated correctly and one collated as if the
numbers were different.

The only different operation carried out on this particular data set is that
the (part) numbers contained "-"'s so I have used replace to remove the -
with null.

I cannot see how this data is different yet Excel does!

Any ideas please....

Just changing the format is not enough. The data will still be Text. Try
this. Place a 1 in any blank cell. Copy that Cell. Highlight the Numbers and
select Paste Special - Multiply. That will convert the numbers from Text to
Numbers. You will then have to drag the Numbers off the pivot table and
refresh your pivot table Twice... Long story but suffice it to say that you
need to refresh twice prefereably with the Numbers field removed... That
should clean up the pivot table.
--
HTH...

Jim Thomlinson


"Dave S" wrote:

Hi Jim

Thanks but I have already tried formatting all the data to the same type;
text and general, but still the same problem.

Dave


"Jim Thomlinson" wrote:

As a guess one set of data is numbers and the other is text... The number 5
is very different from the text string "5". Try to convert them all to number
or text.
--
HTH...

Jim Thomlinson


"Dave S" wrote:

I have created a data set from several (5) different worksheets with the aim
of using a pivot table to sort and collate the data. This is something I have
done many times before.

In this instance when I create the Pivot Table one section of the data is
read by Excel as if it was a a set of different numbers. So I get a Pivot
Table with 4 sets of data collated correctly and one collated as if the
numbers were different.

The only different operation carried out on this particular data set is that
the (part) numbers contained "-"'s so I have used replace to remove the -
with null.

I cannot see how this data is different yet Excel does!

Any ideas please....

Hi Jim

Before I saw your post I have found a fix but I will try yours as well.

The fix was to save the data as a CSV Comma Deliminated file. Apperently
This has the effect of removing all formatting so when opening backinto Excel
it is 'clean'. It is probably the same as you suggested but a different
approach.

Thanks for your help :)

Regards

Dave




"Jim Thomlinson" wrote:

Just changing the format is not enough. The data will still be Text. Try
this. Place a 1 in any blank cell. Copy that Cell. Highlight the Numbers and
select Paste Special - Multiply. That will convert the numbers from Text to
Numbers. You will then have to drag the Numbers off the pivot table and
refresh your pivot table Twice... Long story but suffice it to say that you
need to refresh twice prefereably with the Numbers field removed... That
should clean up the pivot table.
--
HTH...

Jim Thomlinson


"Dave S" wrote:

Hi Jim

Thanks but I have already tried formatting all the data to the same type;
text and general, but still the same problem.

Dave


"Jim Thomlinson" wrote:

As a guess one set of data is numbers and the other is text... The number 5
is very different from the text string "5". Try to convert them all to number
or text.
--
HTH...

Jim Thomlinson


"Dave S" wrote:

I have created a data set from several (5) different worksheets with the aim
of using a pivot table to sort and collate the data. This is something I have
done many times before.

In this instance when I create the Pivot Table one section of the data is
read by Excel as if it was a a set of different numbers. So I get a Pivot
Table with 4 sets of data collated correctly and one collated as if the
numbers were different.

The only different operation carried out on this particular data set is that
the (part) numbers contained "-"'s so I have used replace to remove the -
with null.

I cannot see how this data is different yet Excel does!

Any ideas please....