+ Reply to Thread
Results 1 to 5 of 5

Problems with sorting andf Pivot Table problems

  1. #1
    Forum Contributor
    Join Date
    10-31-2006
    Location
    Oslo, Norway
    MS-Off Ver
    MS 365 Business
    Posts
    282

    Problems with sorting andf Pivot Table problems

    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.
    Attached Files Attached Files
    Last edited by Saturn; 10-07-2012 at 06:34 AM.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Problems with sorting andf Pivot Table problems

    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
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Problems with sorting andf Pivot Table problems

    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.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Contributor
    Join Date
    10-31-2006
    Location
    Oslo, Norway
    MS-Off Ver
    MS 365 Business
    Posts
    282

    Re: Problems with sorting andf Pivot Table problems

    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

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,043

    Re: Problems with sorting andf Pivot Table problems

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1