Random unique values - excluding duplicates

B1Mike

New Member
Joined
Sep 16, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hello guys, I would like to kindly ask for your help with a problem I've been trying to solve the whole day yesterday.

I need to have a formula that can pick a random value from a range. Then I want to copy those values to a different sheet. The problem is that I want the formula to take into consideration those values which I copied and exclude them from next results, thus not having duplicates. I've already found a random unique formula, but it's working by comparing/ranking rand() numbers, but when I copy and paste them, I don't know how I can avoid getting duplicates. I can't get my head around this no matter how much I think about it. Is there a way to do this?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Thank you, I've checked the link, but unfortunately that doesn't solve the problem. I'm already using this formula, but I need to "extract" the random values and then make sure that the next random values are not the same as the extracted ones.
 
Upvote 0
1. Are you want create random numbers at specific range. Example 10 to 30 ? If yes, Please Tell
2. Why you don't use Rand() function at one column, Then With Rank function at second Column, finding rank of them at first Column.
 
Upvote 0
@B1Mike, welcome to the Forum.

Can you provide more detail? Let's say your range has 100 possible values. Then you can only "extract" 100 numbers without duplication. It's not clear whether you're taking out numbers one at a time, or perhaps in groups of n, but the principle is the same - we can adjust @maabadi 's link to accommodate this.

I'll also reiterate @maabadi's request to please let us know which version of Excel you're using, preferably by updating your account details. If you have 365, there are slicker solutions.

PS - are the numbers in your range unique, or do we need to allow for/avoid duplicates?
 
Upvote 0
Ok, sorry, I will try to make it much more specific. I have an English wordlist of about 15k words. I would like to create a file, where me and others could select what words would the student learn in one lesson. Every lesson should have new words. So the first lesson's vocabulary from random selection would be unique, but then second lesson needs to be checked what words had already been used and exclude them. The range does contain multiple entries, but that could be removed or modified. I would also like to use it to exclude words which the student already knows.

Here's an example of the data and the function I've found: INDEX('#Source'!$B$2:$B$15697;RANK.EQ('#Source'!G3;'#Source'!$G$2:$G$15697)+COUNTIF('#Source'!G3:G15700;'#Source'!G3)-1;1) /the data and the student's vocabulary are different sheets. 1st pic "source sheet"- data source, 2nd pic "student's name"- formula's results, 3rd pic "student's name"- copied results
 

Attachments

  • source.jpg
    source.jpg
    76.3 KB · Views: 26
  • results.jpg
    results.jpg
    45.7 KB · Views: 25
  • vocabulary.jpg
    vocabulary.jpg
    60.6 KB · Views: 23
Upvote 0
I think this working for you.
1. first by Rand() function create random numbers.
2. then Copy and Paste Values Random numbers created at Step 1.
3. then Use this:
Book1.xlsx
ABCDEFG
1No.WordsRandExtracted Words
21Word10.056876365Lesson 1Word3
32Word20.392346771Word13
43Word30.427608558Word15
54Word40.645835068Word18
65Word50.29137681Word10
76Word60.664078136Word19
87Word70.72815862Lesson 2Word21
98Word80.298663347Word11
109Word90.668755135Word20
1110Word100.051623881Word2
1211Word110.593647647Word17
1312Word120.807420256Word22
1413Word130.267255531Word8
1514Word140.416746395Lesson 3Word14
1615Word150.829871596Word23
1716Word160.123205587Word5
1817Word170.920768342Word24
1918Word180.389162719Word12
2019Word190.2874564Word9
2120Word200.228016235Word7
2221Word210.006266237Word1
2322Word220.205372405Word6
2423Word230.552367533Word16
2524Word240.119576208Word4
26
Sheet1
Cell Formulas
RangeFormula
F2:F25F2=INDEX($B$2:$B$25,MATCH(AGGREGATE(14,6,$A$2:$A$25,RANK.AVG(C2,$C$2:$C$25)),$A$2:$A$25,0))
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top