Placing random numbers in numerical order

  • Thread starter Thread starter Bob Mc
  • Start date Start date
B

Bob Mc

I am a new user of excel 2007. I have spread sheet of vehicle #s,
1102-1108, 1824-1828,
3200-2224 etc. Columns from A-M with 25 rows each and open cells in M for
adding and deleting vehicles. I am regularly adding and removing #s as
inventory changes. I want to be able to clear cells when vehicles exit and
add #s in Column M as they return or new #s are added. I want to have the
#s reset in numerical order from column A top to bottom, then B top to
bottom etc. - - Much of the material in the postings is beyond my current
abilities. Please help if you can. B.M.
 
Could you give us a more detailed idea of the layout - may be a small
sample.
I cannot figure out how number in M get to go to A.
best wishes
 
Bernard Liengme said:
Could you give us a more detailed idea of the layout - may be a small
sample.
I cannot figure out how number in M get to go to A.
best wishes
Click to expand...

Perhaps: Sort and filter- Smallest to largest but spanning all columns as
one if possible.1106 3804 3824 3914 3934
1107 3805 3825 3915 3935
1108 3806 3826 3916 3936
1302 3807 3827 3917 3937
1863 3808 3828 3918 3938
1864 3809 3829 3919 3939
1865 3810 3900 3920 3940
1866 3811 3901 3921 3941
1867 3812 3902 3922 3942
1963 3813 3903 3933 3943
1964 3814 3904 3934 3944
1965 3815 3905 3935 3945
1966 3816 3906 3936 3946
1968 3817 3907 3937 3947
1972 3818 3908 3938 3948
1973 3819 3909 3939 3949
3800 3820 3910 3940 5013
3801 3821 3911 3941 5014
3802 3822 3912 3942 5015
3803 3823 3913 3943 5016
 
If I understand you correctly, you can automate this with a
Worksheet_Change event procedure, so the data will be resorted any
time a change is made in the range A1:M25. Right-click on the tab of
the worksheet with your data and choose View Code on the popup menu.
That will open up the VBA editor and the code module for the
worksheet. Paste in the following code:

'<<<<<<<<<< BEGIN CODE
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim Col As Long
Dim SortRange As Range
Const FirstRow = 1 ' Or = 2 if row 1 has column headings

If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Column > 13 Then
Exit Sub
End If
If Target.Row > 25 Then
Exit Sub
End If

Application.EnableEvents = False
Application.ScreenUpdating = False
For Col = 1 To 13
LastRow = Me.Cells(26, Col).End(xlUp).Row
With Me
Set SortRange = .Range(.Cells(FirstRow, Col), _
.Cells(LastRow, Col))
End With
If SortRange.Cells.Count > 1 Then
SortRange.Sort key1:=SortRange(1, 1), _
order1:=xlAscending, HEADER:=xlNo
End If
Next Col
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'<<<<<<<<<< END CODE

Close VBA and return to Excel.

Whenever you change, add, or delete a value within A1:M25 on that
sheet, columns A:M rows 1:25 will be individually sorted in ascending
order.

If row 1 has column headings that should not be sorted, change

Const FirstRow = 1

to

Const FirstRow = 2

If row 1 should be included in the sort, use
Const FirstRow = 1



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
If I have this data

north south east west code
1108 3806 3826 3916 3936
1302 3807 3827 3917 3937
1107 3805 3825 3915 3935
1106 3804 3824 3914 3934

and select all of in, then I can use Data | Sort to tell it to sort by the
'Code" column to get

north south east west code
1106 3804 3824 3914 3934
1107 3805 3825 3915 3935
1108 3806 3826 3916 3936
1302 3807 3827 3917 3937

The last column is sorted but reach row remains the same.
Is this what you wanted?
Bernard
 
Chip Pearson said:
If I understand you correctly, you can automate this with a
Worksheet_Change event procedure, so the data will be resorted any
time a change is made in the range A1:M25. Right-click on the tab of
the worksheet with your data and choose View Code on the popup menu.
That will open up the VBA editor and the code module for the
worksheet. Paste in the following code:

'<<<<<<<<<< BEGIN CODE
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim Col As Long
Dim SortRange As Range
Const FirstRow = 1 ' Or = 2 if row 1 has column headings

If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Column > 13 Then
Exit Sub
End If
If Target.Row > 25 Then
Exit Sub
End If

Application.EnableEvents = False
Application.ScreenUpdating = False
For Col = 1 To 13
LastRow = Me.Cells(26, Col).End(xlUp).Row
With Me
Set SortRange = .Range(.Cells(FirstRow, Col), _
.Cells(LastRow, Col))
End With
If SortRange.Cells.Count > 1 Then
SortRange.Sort key1:=SortRange(1, 1), _
order1:=xlAscending, HEADER:=xlNo
End If
Next Col
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'<<<<<<<<<< END CODE

Close VBA and return to Excel.

Whenever you change, add, or delete a value within A1:M25 on that
sheet, columns A:M rows 1:25 will be individually sorted in ascending
order.

If row 1 has column headings that should not be sorted, change

Const FirstRow = 1

to

Const FirstRow = 2

If row 1 should be included in the sort, use
Const FirstRow = 1



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...

A B C D E F G H I J K L M N
1106 3804 3824 3914 3934 5017 5066 5216 5236 5361 5578 5598 5618 8986
1107 3805 3825 3915 3935 5018 5067 5217 5237 5362 5579 5599 5619 8987
1108 3806 3826 3916 3936 5019 5068 5218 5238 5363 5580 5600 5620 8988
1302 3807 3827 3917 3937 5020 5069 5219 5239 5364 5581 5601 5621 8989
1863 3808 3828 3918 3938 5021 5070 5220 5240 5365 5582 5602 5622 8990
1864 3809 3829 3919 3939 5022 5071 5221 5241 5563 5583 5603 5623 9203
1865 3810 3900 3920 3940 5023 5072 5222 5242 5564 5584 5604 5625 9205
1866 3811 3901 3921 3941 5024 5073 5223 5243 5565 5585 5605 5626 5079
1867 3812 3902 3922 3942 5025 5074 5224 5244 5566 5586 5606 5627 5080
1963 3813 3903 3933 3943 5055 5205 5225 5245 5567 5587 5607 5628 5081
1964 3814 3904 3934 3944 5066 5206 5226 5246 5568 5588 5608 5629
1965 3815 3905 3935 3945 5067 5207 5227 5247 5569 5589 5609 5630
1966 3816 3906 3936 3946 5068 5208 5228 5247 5570 5590 5610 5631
1968 3817 3907 3937 3947 5069 5209 5229 5249 5571 5591 5611 5632
1972 3818 3908 3938 3948 5070 5210 5230 5355 5572 5592 5612 5633
1973 3819 3909 3939 3949 5071 5211 5231 5356 5573 5593 5613 8931
3800 3820 3910 3940 5013 5072 5212 5232 5357 5574 5594 5614 8932
3801 3821 3911 3941 5014 5073 5213 5233 5358 5575 5595 5615 8933
3802 3822 3912 3942 5015 5074 5214 5234 5359 5576 5596 5616 8984
3803 3823 3913 3943 5016 5075 5215 5235 5360 5577 5597 5617 8985

I would like to be able to enter #s in column N as shown and have them
correctly placed in column G in correct numeric
order. Therefore the last three #s in column G would have to shift to the
top of column H etc... and so on to back to column N. (The current VBA just
deletes and replaces them). When I clear a cell or delete a # I would like
the reverse adjustment to take place. Thanks Bob M.
 
Just how large do you anticipate the dataset to eventually become?

I can give you something using formulas, but it will take two copies of the
dataset.

A "working" copy, which you will use to add and delete numbers, and a
"presentation" copy, which will display the numbers from the original
dataset in sorted order.

You won't be able to work on the "presentation", copy since that will be an
array of formulas whose sole function is to display.

If you're interested, post back with your best guess as to the number of
columns that the dataset might contain (assuming a max of 25 rows).
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Chip Pearson said:
If I understand you correctly, you can automate this with a
Worksheet_Change event procedure, so the data will be resorted any
time a change is made in the range A1:M25. Right-click on the tab of
the worksheet with your data and choose View Code on the popup menu.
That will open up the VBA editor and the code module for the
worksheet. Paste in the following code:

'<<<<<<<<<< BEGIN CODE
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim Col As Long
Dim SortRange As Range
Const FirstRow = 1 ' Or = 2 if row 1 has column headings

If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Column > 13 Then
Exit Sub
End If
If Target.Row > 25 Then
Exit Sub
End If

Application.EnableEvents = False
Application.ScreenUpdating = False
For Col = 1 To 13
LastRow = Me.Cells(26, Col).End(xlUp).Row
With Me
Set SortRange = .Range(.Cells(FirstRow, Col), _
.Cells(LastRow, Col))
End With
If SortRange.Cells.Count > 1 Then
SortRange.Sort key1:=SortRange(1, 1), _
order1:=xlAscending, HEADER:=xlNo
End If
Next Col
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'<<<<<<<<<< END CODE

Close VBA and return to Excel.

Whenever you change, add, or delete a value within A1:M25 on that
sheet, columns A:M rows 1:25 will be individually sorted in ascending
order.

If row 1 has column headings that should not be sorted, change

Const FirstRow = 1

to

Const FirstRow = 2

If row 1 should be included in the sort, use
Const FirstRow = 1



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...

A B C D E F G H I J K L M N
1106 3804 3824 3914 3934 5017 5066 5216 5236 5361 5578 5598 5618 8986
1107 3805 3825 3915 3935 5018 5067 5217 5237 5362 5579 5599 5619 8987
1108 3806 3826 3916 3936 5019 5068 5218 5238 5363 5580 5600 5620 8988
1302 3807 3827 3917 3937 5020 5069 5219 5239 5364 5581 5601 5621 8989
1863 3808 3828 3918 3938 5021 5070 5220 5240 5365 5582 5602 5622 8990
1864 3809 3829 3919 3939 5022 5071 5221 5241 5563 5583 5603 5623 9203
1865 3810 3900 3920 3940 5023 5072 5222 5242 5564 5584 5604 5625 9205
1866 3811 3901 3921 3941 5024 5073 5223 5243 5565 5585 5605 5626 5079
1867 3812 3902 3922 3942 5025 5074 5224 5244 5566 5586 5606 5627 5080
1963 3813 3903 3933 3943 5055 5205 5225 5245 5567 5587 5607 5628 5081
1964 3814 3904 3934 3944 5066 5206 5226 5246 5568 5588 5608 5629
1965 3815 3905 3935 3945 5067 5207 5227 5247 5569 5589 5609 5630
1966 3816 3906 3936 3946 5068 5208 5228 5247 5570 5590 5610 5631
1968 3817 3907 3937 3947 5069 5209 5229 5249 5571 5591 5611 5632
1972 3818 3908 3938 3948 5070 5210 5230 5355 5572 5592 5612 5633
1973 3819 3909 3939 3949 5071 5211 5231 5356 5573 5593 5613 8931
3800 3820 3910 3940 5013 5072 5212 5232 5357 5574 5594 5614 8932
3801 3821 3911 3941 5014 5073 5213 5233 5358 5575 5595 5615 8933
3802 3822 3912 3942 5015 5074 5214 5234 5359 5576 5596 5616 8984
3803 3823 3913 3943 5016 5075 5215 5235 5360 5577 5597 5617 8985

I would like to be able to enter #s in column N as shown and have them
correctly placed in column G in correct numeric
order. Therefore the last three #s in column G would have to shift to the
top of column H etc... and so on to back to column N. (The current VBA just
deletes and replaces them). When I clear a cell or delete a # I would like
the reverse adjustment to take place. Thanks Bob M.
 
RagDyeR said:
Just how large do you anticipate the dataset to eventually become?

I can give you something using formulas, but it will take two copies of
the
dataset.

A "working" copy, which you will use to add and delete numbers, and a
"presentation" copy, which will display the numbers from the original
dataset in sorted order.

You won't be able to work on the "presentation", copy since that will be
an
array of formulas whose sole function is to display.

If you're interested, post back with your best guess as to the number of
columns that the dataset might contain (assuming a max of 25 rows).
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------



A B C D E F G H I J K L M N
1106 3804 3824 3914 3934 5017 5066 5216 5236 5361 5578 5598 5618 8986
1107 3805 3825 3915 3935 5018 5067 5217 5237 5362 5579 5599 5619 8987
1108 3806 3826 3916 3936 5019 5068 5218 5238 5363 5580 5600 5620 8988
1302 3807 3827 3917 3937 5020 5069 5219 5239 5364 5581 5601 5621 8989
1863 3808 3828 3918 3938 5021 5070 5220 5240 5365 5582 5602 5622 8990
1864 3809 3829 3919 3939 5022 5071 5221 5241 5563 5583 5603 5623 9203
1865 3810 3900 3920 3940 5023 5072 5222 5242 5564 5584 5604 5625 9205
1866 3811 3901 3921 3941 5024 5073 5223 5243 5565 5585 5605 5626 5079
1867 3812 3902 3922 3942 5025 5074 5224 5244 5566 5586 5606 5627 5080
1963 3813 3903 3933 3943 5055 5205 5225 5245 5567 5587 5607 5628 5081
1964 3814 3904 3934 3944 5066 5206 5226 5246 5568 5588 5608 5629
1965 3815 3905 3935 3945 5067 5207 5227 5247 5569 5589 5609 5630
1966 3816 3906 3936 3946 5068 5208 5228 5247 5570 5590 5610 5631
1968 3817 3907 3937 3947 5069 5209 5229 5249 5571 5591 5611 5632
1972 3818 3908 3938 3948 5070 5210 5230 5355 5572 5592 5612 5633
1973 3819 3909 3939 3949 5071 5211 5231 5356 5573 5593 5613 8931
3800 3820 3910 3940 5013 5072 5212 5232 5357 5574 5594 5614 8932
3801 3821 3911 3941 5014 5073 5213 5233 5358 5575 5595 5615 8933
3802 3822 3912 3942 5015 5074 5214 5234 5359 5576 5596 5616 8984
3803 3823 3913 3943 5016 5075 5215 5235 5360 5577 5597 5617 8985

I would like to be able to enter #s in column N as shown and have them
correctly placed in column G in correct numeric
order. Therefore the last three #s in column G would have to shift to the
top of column H etc... and so on to back to column N. (The current VBA
just
deletes and replaces them). When I clear a cell or delete a # I would like
the reverse adjustment to take place. Thanks Bob M.
Click to expand...
 
The concept here is to transfer the numbers in the "working" dataset to a
*single* column using formulas.
Then, another formula *automatically* sorts the numbers in that single
column into an adjoining column.

And then, yet another formula will take the numbers from the adjoining
sorted single column, and transfer it back into the same configuration as
the original dataset, though in a different location.

And finally, if you wish, you could copy the *data only* from the
"presentation" dataset back over the "working" dataset, so that you might
more easily find specific numbers that you would need to change or delete.

You *must not* make any changes in the "presentation" dataset, or you'll
destroy the formulas there.

To make these formulas a little more simpler, you should *not* have any
blank cells in the original dataset.
To cancel out a number, let's say that you just type in an
X
in the cell. Do the same for empty cells.
All cells in the "working" dataset should either have a number or an "X".

Now, for demo purposes, in a new worksheet, change the width of columns A:AC
to 4.
This will allow you to see both datasets at the same time, and you can watch
how the numbers change in the "presentation" dataset as you make revisions
to the "working" dataset.

Fill A1 to N25 with your numbers.
Type an X in any empty cells.

In A30 enter this formula:
=INDEX($A$1:$N$25,MOD(ROWS($1:1)-1,25)+1,ROWS($1:25)/25)

In B30 enter this formula:
=SMALL($A$30:$A$379,ROWS($1:1))

Select *both* A30 and B30, and copy that 2 cell selection down to row 379.

That gives you a row for each of the cells in the dataset.

You will now see that Column A is a copy of the numbers in the dataset,
while Column B is the *sorted* display of those numbers.

We now want to copy the sorted data in Column B to the "presentation"
dataset, which we will place in P1:AC25.

So, in P1 enter this formula:
=INDEX($B$30:$B$379,MOD(ROWS($1:1)-1,25)+(25*COLUMNS($A:A)-24))

And copy this formula down to P25.

With P1 to P25 selected, drag across to copy that selection to Column AC.

You're now done!

Play around and make changes to the original "working" dataset, and you'll
see the "presentation" dataset *automatically* change to reflect those
revisions.

Don't forget ... no empty cells in the original dataset!


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
 
RagDyeR said:
The concept here is to transfer the numbers in the "working" dataset to a
*single* column using formulas.
Then, another formula *automatically* sorts the numbers in that single
column into an adjoining column.

And then, yet another formula will take the numbers from the adjoining
sorted single column, and transfer it back into the same configuration as
the original dataset, though in a different location.

And finally, if you wish, you could copy the *data only* from the
"presentation" dataset back over the "working" dataset, so that you might
more easily find specific numbers that you would need to change or delete.

You *must not* make any changes in the "presentation" dataset, or you'll
destroy the formulas there.

To make these formulas a little more simpler, you should *not* have any
blank cells in the original dataset.
To cancel out a number, let's say that you just type in an
X
in the cell. Do the same for empty cells.
All cells in the "working" dataset should either have a number or an "X".

Now, for demo purposes, in a new worksheet, change the width of columns
A:AC
to 4.
This will allow you to see both datasets at the same time, and you can
watch
how the numbers change in the "presentation" dataset as you make revisions
to the "working" dataset.

Fill A1 to N25 with your numbers.
Type an X in any empty cells.

In A30 enter this formula:
=INDEX($A$1:$N$25,MOD(ROWS($1:1)-1,25)+1,ROWS($1:25)/25)

In B30 enter this formula:
=SMALL($A$30:$A$379,ROWS($1:1))

Select *both* A30 and B30, and copy that 2 cell selection down to row 379.

That gives you a row for each of the cells in the dataset.

You will now see that Column A is a copy of the numbers in the dataset,
while Column B is the *sorted* display of those numbers.

We now want to copy the sorted data in Column B to the "presentation"
dataset, which we will place in P1:AC25.

So, in P1 enter this formula:
=INDEX($B$30:$B$379,MOD(ROWS($1:1)-1,25)+(25*COLUMNS($A:A)-24))

And copy this formula down to P25.

With P1 to P25 selected, drag across to copy that selection to Column AC.

You're now done!

Play around and make changes to the original "working" dataset, and you'll
see the "presentation" dataset *automatically* change to reflect those
revisions.

Don't forget ... no empty cells in the original dataset!


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
Click to expand...
 
Back
Top