ad: M2Ant-1

Sorting data in Excel

Discussion in 'Computers, Hardware, and Operating Systems' started by N3DT, Oct 26, 2019.

ad: L-HROutlet
ad: l-rl
ad: Left-2
ad: L-MFJ
ad: MessiPaoloni-1
ad: Left-3
ad: Subscribe
  1. N3DT

    N3DT Ham Member QRZ Page

    So I'm trying to sort a list that includes columns of time names, etc and one includes distance, a number value. I select the whole data I want to sort, click on 'Sort' under 'Data', and a dialog box opens with 'Sort by' I select column J where the distance number value is, leave 'sort on values', and then 'order' I change from A-Z to Z to A so it will sort largest first, click OK and it sorts, but by the first number. That is it will sort 1000 above 60. How do I make it sort by the actual number value, larges one first, not the largest first number first. This is idiotic, don't think I've had this problem before.

    Now I'm importing this data from WSPR.net from the web site, so there are blank spaces in the numbers, but that shouldn't make any difference. I know sometimes I have to replace all those blank place holders with actual blank space, but I've tried that and it still sorts by largest first number, not by actual value????
     
  2. N1OOQ

    N1OOQ Ham Member QRZ Page

    Either make sure your data are all numbers, not text, or "sort anything that looks like a number, like a number ."
     
    KT5WB likes this.
  3. N3DT

    N3DT Ham Member QRZ Page

    Yeah, I even went to 'format cells' and changed it to 'number' for that column. Didn't make a difference. I'm really thinking it's something to do with the import. I've run into this before.
     
  4. N1OOQ

    N1OOQ Ham Member QRZ Page

    I'm not sure if format cells would work if there were embedded spaces in the data. My guess is no.
     
  5. N3DT

    N3DT Ham Member QRZ Page

    I did try removing the embedded spaces, but no luck. I may try the auto replace, I've done that before and it seemed to work it will replace all the dead spaces in a hurry.
     
  6. N3DT

    N3DT Ham Member QRZ Page

    Funny thing is I tried the find and replace and it wouldn't find the spaces. I can see they're there in the box at the top and I can remove them there also with the backspace, or even in the cell, but it still does nothing, it sorts by the first digit no matter what I do. I feel like I'm locked in some sort of dead space excel here.

    I even found a way to sort by first digit by using a helper column, but that's not what I want to do. Bizarre.
     
  7. N3DT

    N3DT Ham Member QRZ Page

    It's something to do with pasting the info from wspr into excel. I try multiplying the pasted number by 1 into another column and I get VALUE!. So what is showing up as a number must actually be a picture or something indescribable. I cannot figure out how to remove the web site formatting. I've tried pasting with source and destination formatting and nothing works. I've tried copying it from excel into another column, worksheet, etc to no avail.
     
  8. N3DT

    N3DT Ham Member QRZ Page

    HA, you have to import the data as 'get external data' and select the web site, select the table and import it. Then it comes in as real data instead of whatever is in the web page.
     
  9. AF9US

    AF9US Premium Subscriber QRZ Page

    N3DT:

    Are what look like numbers in your data, really numbers; for example: lower case l (el) being used for 1 (number 1), and a letter o or capital O being used for 0 (zero).

    Space characters can only be detected by the characters that surround them; for example: 1 0, can be detected by searching for 1 0 and replacing with 10 (number 10).

    You are definitely in the "fun" area of Excel! Once you get the hang of it, it turns into an adventure.

    Regards,
    Bernie, AF9US
     
  10. N3DT

    N3DT Ham Member QRZ Page

    No, I could re-type the data into the cell and it always sorted the same. It must have been some strange type of formatting for the cell that I couldn't und0, it sure did look right though. It did sort, but more like it was text and always sorted with the first digit. I just happened upon the 'import data' by fooling around, I think it was a mistaken click. I doubt if I'll remember it though.
    In the past I imported data by copy and paste and was always able to remove the leading and following blanks with the 'find and replace', but this one didn't work.
    You can really do a lot with Excel. I do most of my math in it, it's better than a calculator because you can string things together, it just takes time once in a while to figure out how to get the formula right.
    AC6LA really knows how to use Excel. Way past me.
     

Share This Page