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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ?
 
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.
 
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 ?
 
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
 
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
 
Back
Top