[Solved] Using "RANDBETWEEN" and I want no dups listed.

[infoseeker]

Hello Everyone,
I just joined this forum and it appears to have a ton of great content. I looked at many of the search results related to the RANDBETWEEN command but did not see how it fit my exact problem. So, here is my issue and maybe someone can tell me exactly how to fix it?

You see I'm using the following in Calc and it works perfectly (so far):

=CONCATENATE(INDIRECT("A"&RANDBETWEEN(16;215));" ";INDIRECT("L"&RANDBETWEEN(16;215)))

Which randomly assigns a cell the value of a cell from "A" then a number between 16 and 215. So, if Cell A22 is assigned then it pulls up that cells value ("This is Test #1") and then puts it together with a cell from "L" and a number between 16 and 215. So, lets say L25 is assigned and that pulls up that cells value ("for Bob") Which when combined comes out to "This is a Test #1 for bob". I have it display 2 combined cells at a time, five times. So, I may see:

 "This is Test #2 for Bill"
 "This is Test #1 for Bob"
 "This is Test #17 for Chuck"
 "This is Test #2 for Bill"
"This is Test #25 for Charlie"

See my problem? It's showing me dups sometimes. (In actuality, I see dups from a larger range of numbers, this is just my simple example)

I want the above to continue to give me random numbers for "A" and "L" BUT I want it to use each number once WITHOUT dups. I'm pretty confident that this can be done but I'm not sure how to do it myself.

I'm hoping someone can tell me exactly what to put in my formula above to get it to do what I want it to do.

Thank you in advance for your time.

Robert

[Villeroy]

uniq_random.ods

(19.46 KiB) Downloaded 139 times

[infoseeker]

Thank you for the fast reply and your example was interesting. But, looking at it I do not see how I will be able to use more then 26 pieces of data. A-Z based on A1-A26. I need to use 200 or more pieces of data like in my example. Can you show me how to do that?

Thank you Villerroy.

Robert

[infoseeker]

Ooooh, I get it. So, row "A" represents my data. I need to wake up! So, this solves my problem! I will use this to sort my data. If I hit any roadblocks I will let you know.

Thank you!

Robert