Lotto database of 5005 rows needs sorting

excelnot

New member
Joined
Jan 4, 2020
Messages
19
Reaction score
0
Points
0
Excel Version(s)
2007
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 a moderator:
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
 
Thanks, AliGW, I'll try it.
Much appreciated,
Denise :wave:
 
AliGW, sorry but I couldn't make the formula work. I've attached an image to show what keeps happening.
AliGW.png
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? :confused:
Thanks,
Denise.
 
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.
 
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?
 
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.
 
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 :wave:
 

Attachments

  • AliCombins-1925.xlsm
    232 KB · Views: 14
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, if you go to Sheet 2, there are 6 columns of numbers.
Thanks,
Denise.
 
Actually those are 6 columns of strings that look like numbers.
Ali's second formula is an array formula that needs confirmed in H1 with Shift+Ctrl+Enter not just Enter.
Then can be dragged over to M and down.
 
Thanks, NoS.
I meant to say that this 'workbook' isn't mine, so I have no idea how to alter it. And I'm no Excel expert.
I found it online while looking something that can generate 6-number combos from up to 15 source numbers.
The workbook does that, but I can't do the next step - sorting each line low to high.
I think the project was aimed at sorting words rather than numbers.
Could the formula be changed to do only numbers?
Thanks,
Denise :)
 
Ali, if you go to Sheet 2, there are 6 columns of numbers.

Yes, but you have not showed what you have tried - there isn't an attempted formula anywhere.

The columns on sheet 2 contain text and are already in ascending order as far as I can see. However, as I stated before, my second formula would sort them and convert them into numbers if entered in H1 and drag copied across and down (note what NoS said in post #11 about entering the formula). In the attached I have done this for a few lines.

If this isn't what you want, then I am sorry, but I am completely lost.
 

Attachments

  • AliCombins-1925 AliGW.xlsm
    230.3 KB · Views: 6
Sorry, did I say ascending order for columns? It's rows I want to sort, ascending, so the lowest number in each row is in column A.
And no, there aren't any formulas on Sheet 2, because I don't know how to do them. Which is why I asked the question in my first post.
As I said to NoS, it's not my workbook, I just found it online.
All I did was follow the instructions in the text boxes and the 6 columns of data appear on Sheet 2.
Thanks,
Denise.
 
OK - so in the sample file, the text that appears in columns A to F is already in ascending order. Did you look at the attachment to post #11? Does it do what you want? If not, what is it that you need? Is it the conversion of the text values in columns A to F into number values? Please try to be specific with your answer. I realise you are getting frustrated, but we are trying to help. :)
 
…or a short macro or two. See attached Sheet2 buttons.
It takes a few seconds.
I've converted the numbers-as-text to numbers, but it's not necessary.
 

Attachments

  • ExcelGuru10406AliCombins-1925.xlsm
    230.9 KB · Views: 11
p45cal, thanks so much! That's exactly what I what I want!
Denise :first:
 
The formula will work if you just adjust the cell format in Sheet 2 from General to Number? Are you hoping the formula would do that for you before it sorts?
 
Thanks Bob. Which formula are you referring to?
 
Back
Top