Can I do this in Calc?

users
#1

I have a list of names in column A.

I would like like to click something and produce a list of random pairs,
say in col B.

For example, suppose I have

Jack
Jill
Sam
Susan
Bill
Joe

I click and get

Jack, Jill
Joe, Susan
Sam, Bill

I don't save the result, and the next tie I run it, I get

Sam, Jack
Bill, Joe
Susan, Jill

Thanks.

#2

What you are doing is really just shuffling your six names.
o Do you want to retain the names in their original order in column A? If so, copy the names to column B.
o In column C, enter =RAND() and fill it down the relevant part of the column.
o Select the relevant rows in columns B and C.
o Go to Data | Sort... and sort by column C.

The values in column B will be shuffled according to the random values in column C. An incidental effect of this is to recalculate the random values in column C, so all you need to do to produce another shuffling is to repeat the sort process, which will use those new random values.

If you actually need the values in pairs as you have described, you can use something like
=INDIRECT("B"&ROW()*2-1)&", "&INDIRECT("B"&ROW()*2)
in another column.

I trust this helps.

Brian Barker

#3

Yes, this can be done in Calc

First,assign a unique number to the names, i did put those number in column B, so i get:
Names Seqeuence
Jack 3
Jill 2
Sam 1
Susan 4
Bill 6
Joe 5

Then, i have this foruma in D2:
=INDIRECT("A"&TEXT(B2+1;"0"))
which i copy from B2 to B3..B7
i get:
Names Seqeuence
Jack 3 Sam
Jill 2 Jill
Sam 1 Jack
Susan 4 Susan
Bill 6 Joe
Joe 5 Bill

Last bit:
In E5: =D2&","&D5
Copy this from D2 to D3..D4

Result:
Names Seqeuence
Jack 3 Sam Sam,Susan
Jill 2 Jill Jill,Joe
Sam 1 Jack Jack,Bill
Susan 4 Susan
Bill 6 Joe
Joe 5 Bill

Next time, the sequence is different, resulting in:
Names Seqeuence
Jack 6 Joe Joe,Jill
Jill 3 Sam Sam,Bill
Sam 1 Jack Jack,Susan
Susan 2 Jill
Bill 5 Bill
Joe 4 Susan

#4

Brian Barker has written on 7/4/2014 8:11 PM:

I have a list of names in column A. I would like to click something
and produce a list of random pairs, say in col B.

For example, suppose I have

Jack
Jill
Sam
Susan
Bill
Joe

I click and get

Jack, Jill
Joe, Susan
Sam, Bill

I don't save the result, and the next tie I run it, I get

Sam, Jack
Bill, Joe
Susan, Jill

What you are doing is really just shuffling your six names.

Well, actually, it's not. :slight_smile: I want to create a list of pairs from a
subset of the names in column A.

o Do you want to retain the names in their original order in column
A? If so, copy the names to column B.
o In column C, enter =RAND() and fill it down the relevant part of the column.

I get a bunch of numbers < 1.

o Select the relevant rows in columns B and C.
o Go to Data | Sort... and sort by column C.

That sorts the names in B. But that doesn't give me pairs.

If you actually need the values in pairs as you have described, you
can use something like
=INDIRECT("B"&ROW()*2-1)&", "&INDIRECT("B"&ROW()*2)
in another column.

In addition to what I already have in B and C?

Thanks.

#5

Luuk has written on 7/5/2014 5:01 AM:

I have a list of names in column A.

I would like like to click something and produce a list of random pairs,
say in col B.

For example, suppose I have

Jack
Jill
Sam
Susan
Bill
Joe

I click and get

Jack, Jill
Joe, Susan
Sam, Bill

I don't save the result, and the next tie I run it, I get

Sam, Jack
Bill, Joe
Susan, Jill

Thanks.

Yes, this can be done in Calc

First,assign a unique number to the names, i did put those number in
column B, so i get:
Names Seqeuence
Jack 3
Jill 2
Sam 1
Susan 4
Bill 6
Joe 5

Then, i have this foruma in D2:
=INDIRECT("A"&TEXT(B2+1;"0"))
which i copy from B2 to B3..B7

What am I copying from B2 to B3..B7 if the formula is in D2???

i get:
Names Seqeuence
Jack 3 Sam
Jill 2 Jill
Sam 1 Jack
Susan 4 Susan
Bill 6 Joe
Joe 5 Bill

Last bit:
In E5: =D2&","&D5
Copy this from D2 to D3..D4

Result:
Names Seqeuence
Jack 3 Sam Sam,Susan
Jill 2 Jill Jill,Joe
Sam 1 Jack Jack,Bill
Susan 4 Susan
Bill 6 Joe
Joe 5 Bill

Next time, the sequence is different

Do I change the sequence numbers by hand???

#6

Brian Barker has written on 7/4/2014 8:11 PM:

What you are doing is really just shuffling your six names.

Well, actually, it's not. :slight_smile: I want to create a list of pairs from a subset of the names in column A.

It is: the list of pairs is simply a shuffling of the original set presented as a half-length sequence of pairs instead of a single list. It would help you see what need to be done if you can recognise that.

In column C, enter =RAND() and fill it down the relevant part of the column.

I get a bunch of numbers < 1.

Well done: that's the idea.

Go to Data | Sort... and sort by column C.

That sorts the names in B. But that doesn't give me pairs.

Again, that is the main part of the process: you have shuffled the values, but they are in vertical pairs, not yet horizontal ones.

If you actually need the values in pairs as you have described, you can use something like
=INDIRECT("B"&ROW()*2-1)&", "&INDIRECT("B"&ROW()*2)
in another column.

In addition to what I already have in B and C?

Er, yes. You keep insisting you want the horizontal pairs - and this part just rearranges your shuffled list into the pairs you want. That's all.

Brian Barker

#7

Yes, or you could write a macro which can do this for you .... :wink: