Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at patreon.com/ClubPA
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Bizarre Excel sorting issue

TofystedethTofystedeth veni, veneri, vamoosiRegistered User regular
So a guy here has an Excel file that has data in 2 columns. Column the first is the model of the unit sold, and the second column is the serial number of the unit. When he tries to sort it by serial number however, one row will not sort. We tried all sorts of things, moving the row to another location at the end or middle of the list. Changing the data type in the cells from general to number. Deleting the data which had been copy/pasted in from a Google spreadsheet and hand entering. It would always just leave that row where it was. The only way we could get it to sort that row, was to remove the 'T' from the end of the model number.

Anybody know why the heck Excel would refuse to sort a row where the data in the cell in the column not being sorted by has a 'T' at the end?

edit: the other model numbers are a mix of 3 digit numbers, and 3 digit numbers ending in other letters, such as K or D. Those work fine.

steam_sig.png
Tofystedeth on

Posts

  • KiplingKipling Registered User regular
    edited October 2009
    So a guy here has an Excel file that has data in 2 columns. Column the first is the model of the unit sold, and the second column is the serial number of the unit. When he tries to sort it by serial number however, one row will not sort. We tried all sorts of things, moving the row to another location at the end or middle of the list. Changing the data type in the cells from general to number. Deleting the data which had been copy/pasted in from a Google spreadsheet and hand entering. It would always just leave that row where it was. The only way we could get it to sort that row, was to remove the 'T' from the end of the model number.

    Anybody know why the heck Excel would refuse to sort a row where the data in the cell in the column not being sorted by has a 'T' at the end?

    edit: the other model numbers are a mix of 3 digit numbers, and 3 digit numbers ending in other letters, such as K or D. Those work fine.

    Without the file, that is impossible to replicate. But there is an answer if it is the top row.

    If it is the top row, then Excel is assuming that the first row is the header row and won't sort it. When you remove the T, then Excel thinks that the list has no header, because of the random nature of the data types in the columns.

    If it isn't the top row, the other option is to try sorting portions of the data set - rows 5 through 10. What happens then?

    Kipling on
    3DS Friends: 1693-1781-7023
  • SoggybiscuitSoggybiscuit At the edge of spacetime lies a path with no end.Registered User regular
    edited October 2009
    Another thing to check would be data types. That one row could be set up as a text data type while the others are numbers for example. I always use "General" for ease of sorting when mixing text/numbers.

    Soggybiscuit on
    Steam - Synthetic Violence | XBOX Live - Cannonfuse | PSN - CastleBravo | Twitch - SoggybiscuitPA
  • CrashtardCrashtard Registered User regular
    edited October 2009
    Definitely sounds like a header row issue. When you click Data > Sort, make sure you uncheck the Header Row button. If the row that wasn't sorting was the top row that should solve your issue.

    Crashtard on
    I pinky swear that we will not screw you.

    Crashtard.jpg
  • ueanuean Registered User regular
    edited October 2009
    Yerah. Header rows.

    Insert a header onto each. "MODEL" and "SERIALNO"
    Highlight both.
    In excel 2003, turn on Autofilter under the data tab
    In excel 2007, just click the Filter button on the Home tab.
    Use the dropdown arrows to sort your data.

    uean on
    Guys? Hay guys?
    PSN - sumowot
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    edited October 2009
    The weird thing though was that if that data was moved to another row it would still not sort it. It would just sit where it was while everything else was sorted around it.

    He did some more fooling around with it yesterday after I posted and found that if he moved that row to the bottom before he copied and pasted it into a new excel sheet it wouldn't do it. So it could be some kind of weird header thing. It just behaved in a very strange way.

    Tofystedeth on
    steam_sig.png
Sign In or Register to comment.