0

I am having a problem with getting 100% 'Unique' results in the randomization of a roster of groups.

Is it possible to modify this code that randomizes all the rosters on the spreadsheet so that when a 'Unique' value is obtained, it is left alone?

Right now the desired percentage is at 92% because when I have increased the percentage, the randomization takes too long to generate, or never generates, the desired result.

Sub refresher()
    For Count = 1 To 500
        Calculate

        UniCount = Application.CountIf(Range("A:F"), "Unique")
        DupCount = Application.CountIf(Range("A:F"), "Duplicate")

        Percent = UniCount / (UniCount + DupCount)

        If UniCount / (UniCount + DupCount) > 0.92 Then Exit For
    Next
End Sub

Would it be another IF statement I have to add? Something like, IF value of pair is 'Unique', leave alone, and randomize the rest until they're 'Unique'. I want to make it so that I can randomize for 100% 'Unique' results more effectively.

Is it possible to attach a sample of the spreadsheet?

machinegod
  • 1
  • 1
  • 1
    Possible duplicate of [Unique Random Numbers using VBA](https://stackoverflow.com/q/18543169/11683) – GSerg Nov 05 '19 at 12:37
  • Are you writing "unique" and "duplicate" on the row after you've found your data? Not sure exactly what you're doing in your code... – Cyril Nov 05 '19 at 13:27
  • Additional question, are you outputting your results to a sheet? I would guess by the use of unique/duplicate results, you are outputting *something*... you can always `match()`, `find()`, or loop through that data to ensure that you don't have a match and if so try again. – Cyril Nov 05 '19 at 13:29
  • @Cyril Most likely, the OP has the cells filled with `=RAND` and they `Calculate` them until, by chance, the unique percentage is high enough. – GSerg Nov 05 '19 at 13:30
  • @GSerg that would be my guess as well, but it would make sense to be part of the background to the inquiry. – Cyril Nov 05 '19 at 13:32
  • https://www.excelforum.com/excel-programming-vba-macros/1295212-programming-an-if-condition-in-relation-to-randomizing-rosters.html#post5222989 Here is a link to another post I made involving the same issue. I provided a sample of the worksheet in this thread. – machinegod Nov 05 '19 at 16:00

0 Answers0