Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Lotto database of 5005 rows needs sorting

  1. #1
    Seeker excelnot's Avatar
    Join Date
    Jan 2020
    Posts
    12
    Articles
    0
    Excel Version
    2007

    Lotto database of 5005 rows needs sorting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    The database consists of 15 'hot' numbers in every possible 6x number combination, hence the 5005 rows.
    I'd like to sort all the rows so that each row is in numerical order, low to high.
    This would result in rows that originally look like this:
    12 30 18 14 3 6
    ..looking like this:
    3 6 12 14 18 30

    Is this possible?
    Thank you,
    Denise
    Last edited by Pecoflyer; 2020-01-05 at 10:04 AM.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,367
    Articles
    0
    Excel Version
    Office 365 Subscription
    Assuming your first six numbers are in A1 to F1, in H1 copied across and down:

    =SMALL($A1:$F1,COLUMNS($H1:H1))

    Excel 365 (Windows) 32 bit

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    12
    30
    18
    14
    3
    6
    3
    6
    12
    14
    18
    30
    Sheet: Sheet1
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker excelnot's Avatar
    Join Date
    Jan 2020
    Posts
    12
    Articles
    0
    Excel Version
    2007
    Thanks, AliGW, I'll try it.
    Much appreciated,
    Denise

  4. #4
    Seeker excelnot's Avatar
    Join Date
    Jan 2020
    Posts
    12
    Articles
    0
    Excel Version
    2007
    AliGW, sorry but I couldn't make the formula work. I've attached an image to show what keeps happening.
    Click image for larger version. 

Name:	AliGW.png 
Views:	12 
Size:	24.3 KB 
ID:	9554
    I imported the data from another Excel database. I'm wondering if those numbers might actually be formatted as text, which maybe affects the formula?
    Thanks,
    Denise.

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,367
    Articles
    0
    Excel Version
    Office 365 Subscription
    If they are text (looks like it, as they are left-aligned), this should work:

    =SMALL(--$A1:$F1,COLUMNS($H1:H1))

    If not, attach the workbook, not a picture.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Seeker excelnot's Avatar
    Join Date
    Jan 2020
    Posts
    12
    Articles
    0
    Excel Version
    2007
    Thanks, Ali, I'll try that when I get home.
    Is there any way of telling if Excel numbers are 'formatted' as text?
    It'd be easy enough to center-align a column of numbers, but that wouldn't change their formatting,would it?

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,367
    Articles
    0
    Excel Version
    Office 365 Subscription
    No, but if you are importing data and numbers appear in Excel left-aligned, they are text. Deal with it BEFORE applying any further formatting.
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Seeker excelnot's Avatar
    Join Date
    Jan 2020
    Posts
    12
    Articles
    0
    Excel Version
    2007
    Sorry Ali, couldn't make it work, so I've attached the file here.
    I just noticed the front page says the numbers are text.
    Can that be permanently changed?
    Then, if I want to load different numbers, I won't have to change them from text again.
    Thanks so much for your help,
    Denise
    Attached Files Attached Files

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,367
    Articles
    0
    Excel Version
    Office 365 Subscription
    What are you not able to make work? I don't see any attempted formula in the example.

    The attachment does not resemble what you asked us to work with, namely six columns of numbers that needed reordering, It's entirely different, with six numbers in one cell. I have no idea what you are trying to achieve - sorry.
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #10
    Seeker excelnot's Avatar
    Join Date
    Jan 2020
    Posts
    12
    Articles
    0
    Excel Version
    2007
    Ali, if you go to Sheet 2, there are 6 columns of numbers.
    Thanks,
    Denise.

Page 1 of 2 1 2 LastLast

Posting Permissions

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