Bob Schneider
asked on
Randomly Sort Column in Excel
If I have a column in a spreadsheet that is numbered 1-135 (for instance), how can I sort JUST THAT COLUMN? Basically I want a column in a spreadsheet where every row has a unique random integer that I can use for one layer of sorting.
If all you care about is having a column of random, unique, numbers then you can use this macro. It assumes you want the numbers in column 'A'. Change line 7 if that's not correct. If there's a one-row heading in the column, change line 22 to
For lngRow = 2 To lngLastRow.
For lngRow = 2 To lngLastRow.
Sub RandomNumbers()
Dim lngRow As Long
Dim lngLastRow As Long
Dim colRandom As New Collection
Dim intRand As Integer
Const COL = "A"
Randomize
lngLastRow = Range(COL & "1048576").End(xlUp).Row
Do Until colRandom.Count = lngLastRow
DoEvents
intRand = Int((lngLastRow * 2) * Rnd + 1)
On Error Resume Next
colRandom.Add intRand, CStr(intRand)
On Error GoTo 0
Loop
For lngRow = 1 To lngLastRow
Cells(lngRow, COL) = colRandom(lngRow)
Next
End Sub
If you just want to sort a specific column without affecting the adjacent columns, follow these steps...
1) Select all the cells with values in the column you want to sort excluding the header if any.
2) Right click on one of the selected cell and choose Sort and click on Sort Smallest to Largest or Largest to Smallest as per your requirement.
3) Once you do that, you will get a Sort Warning as shown in the image below and in there choose Continue with the current selection and Sort.
This will sort just the selected column.
1) Select all the cells with values in the column you want to sort excluding the header if any.
2) Right click on one of the selected cell and choose Sort and click on Sort Smallest to Largest or Largest to Smallest as per your requirement.
3) Once you do that, you will get a Sort Warning as shown in the image below and in there choose Continue with the current selection and Sort.
This will sort just the selected column.
ASKER
Thanks all. That Macro will give me what I need. Thanks for the info on sorting just one column. I knew that...I just don't know how to sort it randomly. Regarding populating a unique value, I did that manually (1-135 or however many rows I have). If there another way to populate a column with unique numeric values?
BTW, I used Rand() to assign random numbers. This doesn't necessarily give me integers but I can make that work. The problem was when I try to sort that column it doesn't appear to sort even if I give it a "Number" data type. Any idea why that might be?
BTW, I used Rand() to assign random numbers. This doesn't necessarily give me integers but I can make that work. The problem was when I try to sort that column it doesn't appear to sort even if I give it a "Number" data type. Any idea why that might be?
What do you mean by "sort randomly"? The macro I posted will give a different list of random numbers every time it is run. Do you want those numbers to be sorted so that they are smallest to largest? If so click the blue button in this workbook.
29168651.xlsm
29168651.xlsm
ASKER
No I want the numbers in a random order. Here is what I am after. Let's say I have 3 teams each with 5 participants numbered 1-5. So I have level (1-5), name, school for each entrant. I want to sort each team's participants by level. So I will have all team's 1st athletes and then all team's 2nd athletes, and then all team's third athletes, etc. Then what I want to do is put all the 1's in random order, followed by all the 2s in random order, etc. See attached workbook. The first sheet is what I start with. The second sheet is what I get when I sort by level. What I want to do next is randomize each level.
This will be used for multiple events with up to 30 teams with as many as 10 levels or more so a clean way to do this final sort is what I am after.
Thanks!
random-sort.xlsx
This will be used for multiple events with up to 30 teams with as many as 10 levels or more so a clean way to do this final sort is what I am after.
Thanks!
random-sort.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thank you!
You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
Experts Exchange Top Expert VBA 2018, 2019
Experts Exchange Distinguished Expert in Excel 2018
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
Experts Exchange Top Expert VBA 2018, 2019
Experts Exchange Distinguished Expert in Excel 2018
in general, try populate a unique value (not necessary to be 1 to 135, or is it a must to have values between 1 to 135?) in that column and then you can sort it accordingly.