Thread: RANDOM Sort based on criteria

I am in dire need of what I should do to do a sort based on the presence or absence of data. Here's what I am trying to do: I am creating a spreadsheet to randomly assign a list of golfers to foursomes. The list will change from day to day with the number of players ranging from 4 players to 50 players on any given day. I have the spreadsheet pretty much finished except I can't establish a button to do the sort routine. I have the names in column B, no certain number of names each day, beginning in B8, and the RAND() in column C beginning in C8 through C50. I need to know the last filled cell in column B and have the random sort start in the adjacent cell in column C and include column B cells 8 through the last cell with a name in it. It will be the same cell number for both B and C. I would like a button to click that would do this, I hope you understand all this, The way I do it now is to enter the names in column B, highlight the numbers in column C from C8 through the number adjacent to the last name entered. I click on the ascending sort button and it tells me it has detected data in an adjacent field and asks if I want to include it in the sort and I check the box to include it.

Hi Technot and welcome to the forum,

The best way to help us help you is to attach a sample (or your current) workbook. That way we can see where the names are and where they would go after a random sorting of them.

To attach a sample file, click on the "Go Advanced" below the message area and then on the Paper Clip Icon above the Advanced Message Area. This will open a window that will allow you to point to the file to be attache/uploaded so we can examine it and do some real help.

I think you are are the right track with putting Rand() next to the names and the sorting by this random number. This is how I do random sorts also. The problem is understanding the rest of the puzzle. Do you know how to code? Are you only using formulas? Do you have links between your names and the scorecard or scoresheet so moving the names around is bad. Too many questions without a sample to see how you have designed your stuff.

For those of you who read my posting I thank you. I have been asked to attach a copy of the Excel Workbook that I have been working on. I believe I told you that my Excel is a 2003 version, never used it very much so I didn't update it. I hope I can read any files that you may send me.

I believe when you see my spreadsheet it will be more clearer as to mwhat I am trying to do that was my explanation. I guess the important things to remember is that the players list will change every day based on who signs up to play the day before and, as previously stated, the number of players could be from 6 or 8 players to as many as 50.
The button to do what I need the spreadsheet to do is in B1, and is titled "CLICK TO ASSIGN FOURSOMES"

Thanks again to all who have looked at this.

Hi Technot,

Find the attached that has VBA code behind a Module. Click the Button in Column B and it will randomly sort all and any names in Columm B.

Hi Technot,

I helped a guy in Houston with his weekend matches last year. I'm attaching his spreadsheet with the names changed to protect the players.

We had a method where all possible players were listed on a players info sheet and he simply double clicked in column A to put an X (the player was playing) or double click on an X and it cleared him. This kept him from trying to spell there names each week. Then on the next sheet we clicked on Get the players and them on make a random draw. Scorecards were printed from this .. etc.

He had a very elaborate system - that may give you some ideas. I've created about 7 of these "systems" now and wouldn't use his, but the double clicking the players names was very cute.

Find it attached.

I'm having trouble getting the macro to run. It says "Can't execute code in break mode". The screen showing has [break] after the title at the top. I can't get anything on the page to move. The screen is frozen. This is happening when I click the button.

I thought about putting the entire roster in the sheet but decided against that because the proshop generates a sheet each day with those who have registered and I wanted to keep the listing as small as possible. I will look at the one you did for the guy in Houston, once I can get mine working. Can't get it to run the macro, I believe I have enabled everything. I don't understand the break code statement.

When I click on the button it gives me a screen that shows some coding that has option explicit at the top. It also gives an error message htat says Compile Error: Variable not defined. In the coding X1sortonvalues is highlighted. I assume the error message is speaking to this coding.

Hi,
Normally a Break Mode is when you have looked at the code and clicked in the left margin. This will highlight a line of code and the code will run down to this point and stop or break.

Here comes the file again. I just ran it and I'm pretty sure there is not a break in the code.

or http://support.microsoft.com/kb/177828

Here is the code I put in a module.

Sub RandomFoursomes()
Dim LastRow As Double

LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range(Cells(8, "C"), Cells(LastRow, "C")).ClearContents

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(8, "C").Formula = "=Rand()"
Cells(8, "C").Copy Destination:=Range(Cells(9, "C"), Cells(LastRow, "C"))

    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("C8"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("B8:C" & LastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

What version of Excel 2003 do you have? Can you run macros? Is it the Student version? Do you have your VBA security set so it will allow running of macros? Can you look at the macro code? Is this Excel workbook the only one running when you get the error message? Can you run the macro without pushing the Button?

Try this one,

I took the SortOn out!!