Random unique values - excluding duplicates

[B1Mike]

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?

 

[maabadi]

Welcome to MrExcel Message Board.
Please Upload your Account Detail to We know what version of Excel you use & guide you based on.
Don't forgot to Save.
For Now Check This Link.
How To Select Random Data From A List Without Duplicates?

 

[B1Mike]

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.

 

[maabadi]

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.

 

[StephenCrump]

@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?

 

[B1Mike]

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

 

[maabadi]

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
	A	B	C	D	E	F	G
1	No.	Words	Rand			Extracted Words
2	1	Word1	0.056876365		Lesson 1	Word3
3	2	Word2	0.392346771			Word13
4	3	Word3	0.427608558			Word15
5	4	Word4	0.645835068			Word18
6	5	Word5	0.29137681			Word10
7	6	Word6	0.664078136			Word19
8	7	Word7	0.72815862		Lesson 2	Word21
9	8	Word8	0.298663347			Word11
10	9	Word9	0.668755135			Word20
11	10	Word10	0.051623881			Word2
12	11	Word11	0.593647647			Word17
13	12	Word12	0.807420256			Word22
14	13	Word13	0.267255531			Word8
15	14	Word14	0.416746395		Lesson 3	Word14
16	15	Word15	0.829871596			Word23
17	16	Word16	0.123205587			Word5
18	17	Word17	0.920768342			Word24
19	18	Word18	0.389162719			Word12
20	19	Word19	0.2874564			Word9
21	20	Word20	0.228016235			Word7
22	21	Word21	0.006266237			Word1
23	22	Word22	0.205372405			Word6
24	23	Word23	0.552367533			Word16
25	24	Word24	0.119576208			Word4
26
Sheet1

Cell Formulas
Range	Formula
F2:F25	F2	=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))