Link to home
Start Free TrialLog in
Avatar of Bob Schneider
Bob SchneiderFlag for United States of America

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.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

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.
User generated image
Avatar of Bob Schneider

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?
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
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
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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