Forum Discussion
No duplicate cells.
Let's say I create a list of 1,000 people and use the function "randbetween" to assign each person their own special code. How can I also make sure, if I were to include new people on my list in the future, that the previous special codes won't be duplicated.
One approach is to generate more numbers than needed, remove dupes, and take the first 1000.
=LET(numbers, RANDARRAY(4000, , 1000, 9999, 1), TAKE(UNIQUE(numbers), 1000))Randarray generates 4,000 numbers betweeen 1000-9999. The 1 is to return whole numbers with no decimals.
One approach is to generate more numbers than needed, remove dupes, and take the first 1000.
=LET(numbers, RANDARRAY(4000, , 1000, 9999, 1), TAKE(UNIQUE(numbers), 1000))Randarray generates 4,000 numbers betweeen 1000-9999. The 1 is to return whole numbers with no decimals.
- Patrick2788 thank you so much! I appreciate it.
- Glad to help. You're welcome!
As variant
=LET( n, 1000, rnd, RANDARRAY(n,,0,1), ind, LARGE(rnd, SEQUENCE(n)), 1000 + XMATCH(ind, rnd) )Between 0 and 1 unique numbers are generated. When only to transform.
First of all, I'm pretty sure that using RANDBETWEEN, with 1 and 1,000 as the lower and upper limits, will NOT generate for you one thousand unique numbers. I just tried it with only 500 entries, and it came up with 388. And that number can change. One of the features of RANDBETWEEN (you can read about it using the hyperlinked word there) is that it's what's called a "volatile" function, meaning it gets recalculated every time you do any other change in the spreadsheet. I.e., any number assigned would change unless you go through a process you can read about via that link to freeze whatever is assigned.
Why use RANDBETWEEN in the first place, one might ask? Why not number them sequentially, perhaps creating an ID that uses sequential numbers (you can begin anywhere) plus, say, the person's initials?
- I am not looking for numbers in chronological order. Random 4 digits number, almost like a pin.
