sorting lottery numbers numerically

[mark91345]

In Excel 2010, I want to sort the lottery numbers (Balls 1 -4) numerically (as it shows in NewBall 1-4). Note that I do NOT want them concatenated into one column, as I need them to remain separate.

What do you recommend?
<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">DATE</td><td style="text-align: center;;">Ball1</td><td style="text-align: center;;">Ball2</td><td style="text-align: center;;">Ball3</td><td style="text-align: center;;">Ball4</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: center;;">NewBall1</td><td style="text-align: center;;">NewBall2</td><td style="text-align: center;;">NewBall3</td><td style="text-align: center;;">NewBall4</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Mon. Apr 02, 2012</td><td style="text-align: center;;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Sun. Apr 01, 2012</td><td style="text-align: center;;">5</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Sat. Mar 31, 2012</td><td style="text-align: center;;">1</td><td style="text-align: center;;">6</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Fri. Mar 30, 2012</td><td style="text-align: center;;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Thu. Mar 29, 2012</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Wed. Mar 28, 2012</td><td style="text-align: center;;">9</td><td style="text-align: center;;">8</td><td style="text-align: center;;">0</td><td style="text-align: center;;">7</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">Tue. Mar 27, 2012</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">0</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">Mon. Mar 26, 2012</td><td style="text-align: center;;">0</td><td style="text-align: center;;">9</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: right;background-color: #A6A6A6;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>

Lottery_4_balls​

 

[Jonmo1]

You can use the SMALL function for that...

IN G2, put
=SMALL($B2:$E2,COLUMNS($A1:A1))
And fill right/down as far as needed

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	DATE	Ball1	Ball2	Ball3	Ball4		NewBall1	NewBall2	NewBall3	NewBall4
2	Mon. Apr 02, 2012	9	2	3	8		2	3	8	9
3	Sun. Apr 01, 2012	5	2	3	2		2	2	3	5
4	Sat. Mar 31, 2012	1	6	0	7		0	1	6	7
5	Fri. Mar 30, 2012	9	2	2	2		2	2	2	9
6	Thu. Mar 29, 2012	7	2	0	7		0	2	7	7
7	Wed. Mar 28, 2012	9	8	0	7		0	7	8	9
8	Tue. Mar 27, 2012	1	5	5	0		0	1	5	5
9	Mon. Mar 26, 2012	0	9	5	9		0	5	9	9
Sheet1

 

[Marcol]

This in G2 would do the same

=SMALL($B2:$E2,COLUMN(A$1))

Drag across to Column J, then Down as required.

However I'm intrigued as to what kind of lottery has duplicate numbers and zero balls ...

 

[Jonmo1]

This in G2 would do the same

=SMALL($B2:$E2,COLUMN(A$1))

Drag across to Column J, then Down as required.

However I'm intrigued as to what kind of lottery has duplicate numbers and zero balls ...

Column(S) is more robust against isertion of columns..

If you just use
COLUMN($A1), and insert a column to the left, then it changes to
COLUMN($B1)
And would then return 2 instead of 1

where COLUMNS($A1:A1) would change to
COLUMNS($B1:B1)
And would still return 1.

 

[Marcol]

@ jonmo1
Good point, but aren't you overthinking things a little?

I wouldn't put a pound on a lottery that had zero balls ...

 

[mark91345]

Hi Jonmo1,
Yes, it works beautifully. Thank you.

So, just for my own understanding, I get the =SMALL($B2:$E2 part,
but I don't understand the COLUMNS($A1:A1)) part, or specifically, what it is referencing (for example, the first one references the "Date" row.

Mark

 

[mark91345]

This is just California's Daily Four lottery. http://www.calottery.com/play/draw-games/daily-4

This in G2 would do the same
However I'm intrigued as to what kind of lottery has duplicate numbers and zero balls ...

 

[Jeff Meyers]

=SMALL has two arguments:

• Array Required. An array or range of numerical data for which you want to determine the k-th smallest value.
• K Required. The position (from the smallest) in the array or range of data to return.

jonmo1's formulas use the COLUMNS function for the second argument. The COLUMNS function returns the number of columns in an array or reference.

In your first column (G), =COLUMNS($A1:A1) returns a value of 1 which returns the smallest number.
In your second column (H), =COLUMNS($A1:B1) returns a value of 2 which returns the second smallest number.
And so on through column (I) for the third smallest number and column (J) for the fourth smallest number.

 

[Jonmo1]

@ jonmo1
Good point, but aren't you overthinking things a little?

I wouldn't say so. It's basically just a habbit.

Better to have and not need
Than to need and not have.

 

[Jonmo1]

Hi Jonmo1,
Yes, it works beautifully. Thank you.

So, just for my own understanding, I get the =SMALL($B2:$E2 part,
but I don't understand the COLUMNS($A1:A1)) part, or specifically, what it is referencing (for example, the first one references the "Date" row.

Mark

Jeff explained it pretty well..

The COLUMNS part isn't referencing the CONTENTS of $A1:A1...
Just the Cells themselves, specifically the count of how many columns are in the range reference.