Have a issue in sorting text data type field in pivot table -Offic

[VJ]

I am importing simple text data from access into excel in a pivot table and a
quarter field represented in numbers in text data type (1,2,3,4) are not
sorted in order in the report. Quarter numbers are appearing as 3,1,2,4. I
tried advance options of the field to set it right but did not suceed? Any
suggestions ?

 

[Ken Wright]

You mean you tried right click on field, choose field settings / advanced /
choose sort ascending as opposed to manual. If so then it may well be that
the 3 has a space or something in it in the source data?

You can drag it manually wherever you like.

 

[VJ]

I tested for space and didn't find one. It is sorting in order in the source.
I tried dragging manually in pivot table and failed. Is there a method by
which you do ?

 

[PeterAtherton]

VJ
You have to test all the data in the original sheet. try this macro

Sub clearSpace()
for each c in selection
c.value = trim(c)
next
End sub

If that dosn't work select the numbers including dates and run this

Sub Test()
for each c in selection
c.value = c * 1 'convert a text number to a number Excel recognises.
next
End Sub

Peter Atherton

 

[Ken Wright]

You should be able to just drag, but if not, try right clicking on the field
that looks out of whack and choose "order" and move left / right / end /
beginning as applicable