Randomly Sort Column in Excel

Bob Schneider
Bob Schneider used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
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.

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.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

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

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Sort-Warning.jpg
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Bob SchneiderCo-Owner

Author

Commented:
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?
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Bob SchneiderCo-Owner

Author

Commented:
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
"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this.
29168651a.xlsm
Bob SchneiderCo-Owner

Author

Commented:
Perfect!  Thank you!
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial