Ask Your Question

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

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 close merge delete


(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

2 Answers

Sort by » oldest newest most voted

answered 2019-04-20 06:37:18 +0200

To generate the unique random numbers in Excel, you need to use two formulas. 1. Suppose you need to generate random numbers without duplicates into column A and column B, now select cell E1, and type this formula =RAND(), then press Enter key.

edit flag offensive delete link more


(Why "Excel"?)
As I see it now, the original questioner had an even number of items and wanted a series of partitions in pairs of different ones of these items where also none of the pairs repeated.
To achieve this he doesn't only need permutations of his original list without any fixed-point, but also a way to select and generate a series of these permutations making sure that no item goes to a place where it already was in one of the previous permutations.
This means: I now read the original question as one equivalent to the problem to create a fixture-list for a soccer-league of 20 teams. We should use one of the well proven programs for the task, and reduce the random aspect to the permutation of the programs per match-day if we want a kind of surprise-effect.
See also: Graph Theory.

Lupp gravatar imageLupp ( 2019-04-22 11:43:37 +0200 )edit

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
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 957 times

Last updated: 2 days ago