Ask Your Question

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

asked 2017-10-27 07:45:00 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-19 03:23:54.388361


(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.

Lupp gravatar imageLupp ( 2017-10-27 16:11:11 +0200 )edit

(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 gravatar imageshoegato ( 2017-10-28 06:02:47 +0200 )edit

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

shoegato gravatar imageshoegato ( 2017-10-28 08:42:23 +0200 )edit

-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.

Lupp gravatar imageLupp ( 2017-10-28 13:13:05 +0200 )edit

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

shoegato gravatar imageshoegato ( 2017-10-29 06:42:01 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-10-27 12:45:35 +0200

Lupp gravatar image

updated 2017-10-30 23:32:37 +0200

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

In your case:
Section "C." "E." (Sorry!) and the example document attached there apply to your question.

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.)

(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.

Spreadsheets for the task:
(German; no understanding of the language needed. Simply replace the names.)
(Author's page in that site:

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.

edit flag offensive delete link more


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 gravatar imagecornouws ( 2017-10-27 13:27:05 +0200 )edit

Obviously: thanks for the extremely helpful part of your answer ;)

cornouws gravatar imagecornouws ( 2017-10-27 14:38:19 +0200 )edit

@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 gravatar imageLupp ( 2017-10-27 16:19:21 +0200 )edit

@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".

Lupp gravatar imageLupp ( 2017-10-28 13:27:18 +0200 )edit

Question Tools

1 follower


Asked: 2017-10-27 07:45:00 +0200

Seen: 8,775 times

Last updated: Oct 30 '17