How to generate random non-repeating cells from a starting range?

[shoegato]

So far I have the following to find a random cell value within the column range I got.

=INDEX(Sheet1.A2:A21, RANDBETWEEN(2,21))

The problem is that I haven’t been able to figure out how to check for repetitions.

I have a list of names from A2:A21 and I want to randomize them so that they do not repeat and they always give a new ordering–I’m using this to create different student pairs daily as far as permutations permit (P(n,r)=P(21,2)
=21!(21−2)!
= 420). Ideally I would do this on the rows and columns following (ex. B2:B21) and would generate 20 more column ranges to cover all permutations. I might be wrong about the number of columns necessary.

[Lupp]

(I don’t consider wiki posts recommendable. Looks modest, but also anonymizes.)
I don’t understand for sure.
-1- “…create different pairs…” Single pairs or a complete breakdown of a class into pairs?
-2- Do you want to regard the order within each pair? (If so for curiosity: why?)
-3- “Ideally I would do this on the rows and columns following (ex. B2:B21)” Need explanation.
-4- Splitting one kind of information over many columns is mostly supposed to be bad design.

[shoegato]

(Edited for better readability by @Lupp)
2-- I’m planning for the everyone to work with someone different the first few weeks of class.
3–Every generated column of student pairs would be a week’s worth of pairings. One day: X,Y work together and so on. Then next day Y,Z work together.
I know sometimes people are absent so I’ll just send my lost lambs to work with a random pair.
4–This is my first time working with spreadsheets. I couldn’t think of a more readable way to organize it.

[shoegato]

@Lupp for one I meant, A complete breakdown of the class into pairs daily.

[Lupp]

@shoegato:
-a- Sorry. My editing of your comment for better line-wrap replaced your avatar by mine. A bug of the askbot software, obviously.
-b- Your statements “-2-”, “-3-” don’t match my respective questions. However, they clear your requirements and make sense. But: What about a class with an odd number of students? Lost-lamb-factory?
-c- You will find usable approaches in the attachment I created when editing my answer.
-d- Freely comment on my suggestions, please.

[shoegato]

@Lupp Thanks. Oh you’re right. For 2, I do not need to regard the order within pairs–that does simplify things a whole lot. I just need the unique combos. As for 3, This is what I pictured I would do in my head:
day/date…
n. student pair…
I guess I don’t really need columns. I just need to find a way to organize days dates and the pairings for that day. I’m learning to think in spreadsheet and I’m slowly breaking down the demo into pieces I can grok.

[Lupp]

Besides this askbot site, there is also a forum on open office suites (including LibO) where you can find solutions.

In your case: [Tutorial] Randomization in Calc (View topic) • Apache OpenOffice Community Forum
Section “C.” “E.” (Sorry!) and the example document attached there apply to your question.

(Editing:)
Though the OQ didn’t answer my questions I prepared this attached demo.
You may take from it what seems useful to you.

(Edit2 regarding the recent explanations by the OQ:)
To create a new partition-into-pairs is simple using the means I already demonstrated, but I would judge it next to impossible (probable solutions at least very inefficient) to definitely avoid any reoccurring pair by Calc means. If a respective guarantee is needed I would resort to user code. (Also not exactly simple. These comments are based on estimations, of course.)

(Edit2:)
(deleted; was not good)
Now hopefully better:
What you actually need has little do do with randomizing. Depending on the preferred words it is nown as a chapter of mathematical graph-theory, or more pragmatically - often needed - the task to schedule (sine-die) the first set of matchdays for a 20-league. In your case the participants are just not expected to fight one another.

Theory: http://www.mathaware.org/mam/2010/essays/FroncekTournament.pdf
Spreadsheets for the task: http://www.excelformeln.de/bilder/Liga-Spielplan.zip
(German; no understanding of the language needed. Simply replace the names.)
(Author’s page in that site: http://www.excelformeln.de/uberuns.html?umenuzahl=14&autor=1)

If randomization shall be included you may first of all randomize the order of names (and leave it at that then) or randomize the order of the pairings you want to apply them in (or both).

The linked sheet is in .xls and contains relics of VBA though there is no active code at all. I made an .ods of it without the macro modules (“hard work”) and it still did its duty. Of cours you may simply reject permission for macros.

[cornouws]

No need to comment in such a vague an unstructured way on different types of help sites, maybe? Thanks!

So I took freedom to improve your answer.

[cornouws]

Obviously: thanks for the extremely helpful part of your answer

[Lupp]

@cornouws: Next time I would prefer you critisize commenting pars of my answers you disagree with, and leave it to me to change them where I can accept your view. You may have noticed that I am not a rookie here.
I critisized this site for its lack of helpful structure more than once, and on some occasions I will do so again. You may also have noticed that there are more than one experienced contributors here who are unhappy with things as they are.

[Lupp]

@shoegato: Sorry. When I saved the demo it still contained an empty BASIC module. Therefore you are prompted for permission for “macros” on opening. Simply reject this. No functionality in the sheet is based on “macros”.