Problems with sorting andf Pivot Table problems

**Saturn** · 10-07-2012, 05:37 AM

In the Input sheet I have a serie of aircraft types. All these cells have the same format, at least apperantly.
When making a pivot table the aircraft type abbreviations are considered differently, even there are no difference in cell format.

I need some explaination to this mystery.

**HaHoBe** · 10-07-2012, 05:48 AM

Hi, Satrun,

those numbers with the little green corner are numbers displayed as text, and you need to convert them into "real" numbers for Excel/VBA like using this macro:

Please Login or Register  to view this content.

When sorting there is the parameter DataOption:=xlSortTextAsNumbers which will take care of sortiung all the resembles a number (also text) as numbers but that won´t have any affect on the Pivot Table.

HTH
Holger

**Ace_XL** · 10-07-2012, 06:04 AM

Non-VBA method...

- Type 1 in a blank cell
- Copy this cell
- Select range of dat (on 'input' sheet)
- Paste special -- Multiply

This will convert all your data to numbers.

Re-draw the pivot table. Refreshing the existing pivot table will not work.

**Saturn** · 10-07-2012, 06:06 AM

Thanks a lot Holger!

Before I run the macro I have two comments:
- why is those numbers displayed as text when the cells appear to be in General format?
- also I dont want the abbreviations be considered as numbers as I have also other abbreviation in the serie which is not numbers, for
example DC9, F28 and so on. This shouldnt affect the order in the pivot table.

So could you look at this again.... ?

Saturn

**HaHoBe** · 10-07-2012, 07:47 AM

Hi, Saturn,

I encounter this mostly after importing SAP and ANSI files into Excel due to my needs. In Germany we do have postal codes which are 5 digits but may start of with a leading zero. To maintain this information I use procedures which import this column (as well as Dates and serial numbers) as text with leading zeros. When trying to group Dates which are displayed as text Excel won´t allow you to group them to Months, Quarters, or Years (which I need for my report).

I can´t tell why the information in your workbook is partly "correct" and partly Numbers as Text (maybe two imports or cells formatted as text before import and a part of these (showing "real" numbers) worked on after changing the cell format to General).

Adding to Ace_XL Transpose and Add or Subtract 0 should work too. But if used on Dates the formatting will be gone thereafter and the long values of the date will be displayed. That´s the reason I rely on macros and change this information accordingly in my imports within the macro (I know which columns need to be reworked and if done within the macro I can´t forget about it later on).

HTH
Holger