OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Mixing Three Lists To Give All Permuations? [SOLVED]

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
seanspotatobusiness
OOo Enthusiast
OOo Enthusiast


Joined: 31 Oct 2005
Posts: 176

PostPosted: Wed Jan 31, 2007 4:47 pm    Post subject: Mixing Three Lists To Give All Permuations? [SOLVED] Reply with quote

I'm currently creating redirects on Wikipedia for all the permutations of the two-hybrid screening article title. It covers E. coli and yeast (specifically known as Saccharomyces cerevisiae) two hybrid screening, but the combinations of words I need to put into titles are; (E. coli/Escherichia coli/bacterial/bacteria/yeast/Saccharomyces cerevisiae/S. cerevosoiae) (two hybrid/two-hybrid) (*nothing*/test/screen/screening/method/analysis). In total, that's 7x2x6 = 84 possible permutations! Even with clever use of the back button and copy and paste, that's going to be a drawn-out laborious task to do by hand. There's a program that can use a list of article names and turn that list into a bunch of redirects on Wikipedia. My problem is how to create that list. Is there some way I can use the Calc program to turn the three of options above into a list of the 84 permutations?

Last edited by seanspotatobusiness on Mon Nov 12, 2007 8:54 am; edited 1 time in total
Back to top
View user's profile Send private message
Metalrat
General User
General User


Joined: 15 Jan 2007
Posts: 11

PostPosted: Wed Jan 31, 2007 6:26 pm    Post subject: Reply with quote

Hmm, I think finding a suitable program or macro will actually take far longer than "biting the bullet" on this one, especially if you sequence the tasks to aid workflow.

I had a quick play with this, setting up a Calc spreadsheet with a 7*6*2 permutation took me about 5 minutes.

Forgive me if this seems obvious, but this is the approach I took.
*Note the cell entries are arbitrary, really chosen to avoid problems with repeated "Find & Replace" operations later.
--------------------------------------------------------------------------------------------------
Set up Column A with 2 cells containing text "A1", hilight them and drag to cover up to row 12.
Repeat for Rows 13,14 with characters "B1", and drag them out to cover Row 24.
.....repeat, changing the text as appropriate, 'til you have 84 rows.

Moving on to Column B, populate Cells B1-B6 with numbers 101-106.
Hilight Cells B1-B6, right click and select Copy.
Hilight Cells B7-B84, right click and select Paste.

Moving on to Column C, populate Cells C1-C2 with "XA" and "YA" respectively.
Hilight Cells C1-C2, right click and select Copy.
Hilight Cells C3-C84, right click and select Paste.

---------------------------------I worked up to this point, it took about 5 minutes

From here, it's just a case of running "Find and Replace All" operations as appropriate, and saving in your required format. I would guess at a further 5 minutes.

------------------------------------------------------------------------------------------------

Luck
Metalrat
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Feb 01, 2007 12:51 am    Post subject: Reply with quote

Just because, I created a semi-generic spreadsheet that will create the permutations from 3 lists of the possible values.

The user would need to fill in the lists with their values, then copy/fill the row of calculations as far down as necessary.

Thus if someone needs to do this regularly, it should only be a minute or so to fill in the lists, and the spreadsheet will automatically create the new permutations.

I will upload it to mytempdir if anyone is interested - let me know.
Back to top
View user's profile Send private message
seanspotatobusiness
OOo Enthusiast
OOo Enthusiast


Joined: 31 Oct 2005
Posts: 176

PostPosted: Thu Feb 01, 2007 8:05 am    Post subject: Reply with quote

I don't understand how to make your spreadsheet work. Suppose I had three lists:

1:
A
B

2:
1
2

3:
y
Z

How do I use your spreadsheet to make:

A1Y
A1Z
A2Y
A2Z
B1Y
B1Z
B2Y
B2Z?
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Feb 01, 2007 9:13 am    Post subject: Reply with quote

username132 wrote:
1:
A
B

2:
1
2

3:
y
Z

Put A and B in I3 and I4

1 and 2 in J3 and J4

Y and Z in K3 and K4

(you'd add any more in I5, J5, K5 and so on down)

Then the permutations are in column G, A/1/Y and so on. I used the / separator as that was in your original post. You can change that by editing G2 and then filling it down.

You only have 8 permutations, so you can delete A10 to G10 and below. If you have lots of permutations, you need to copy A10 to G10 further down, as prompted by cell L2.

Mess around with it, if you still can't make sense of it, let me know. I think it would benefit from several improvements, but I'll await your comments.

If you want some explanation of how it works, let me know.
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Feb 01, 2007 1:35 pm    Post subject: Reply with quote

I've uploaded an improved (I hope) version, with cell protection, hidden columns, a bit of formatting, etc, at:

http://www.mytempdir.com/1197593
Back to top
View user's profile Send private message
seanspotatobusiness
OOo Enthusiast
OOo Enthusiast


Joined: 31 Oct 2005
Posts: 176

PostPosted: Tue Feb 20, 2007 3:39 am    Post subject: Reply with quote

Sorry that it's been a while! That's great! In the end I did the hard way, but I'll certainly be keeping this file for similar problems in the future.

What I'm not sure about it where the A-H columns are and why the formula specifies
;D3&$M$2&E3&$M$2&F3) when columns D, E and F are missing.

Thank you very much for your time!
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Wed Feb 21, 2007 1:05 am    Post subject: Reply with quote

Columns A to H are hidden. To just use the spreadsheet, you don't need to see what they're doing, so I often hide columns (or rows) so that the user can focus on using.

If you want to see them, click on column I (the name box above will show I1:I65536 as the range selected) but don't release the mouse button, then move the cursor over to the next grey box to the left of the I, (the name box above will show A1:I65536) then release, then right-click on column I, and choose Show.

Columns A, B & C create lists of numbers to get the pattern of permutations.

Columns D, E & F convert the number patterns to repeating lists of the names.

Column G is blank.

Column H is a counter used by columns D, E & F to get the names.

Column L concatenates the names in columns D, E & F with the separator in cell M2.

Does that help?
Back to top
View user's profile Send private message
seanspotatobusiness
OOo Enthusiast
OOo Enthusiast


Joined: 31 Oct 2005
Posts: 176

PostPosted: Mon Nov 12, 2007 8:53 am    Post subject: Reply with quote

RickRandom wrote:
Columns A to H are hidden. To just use the spreadsheet, you don't need to see what they're doing, so I often hide columns (or rows) so that the user can focus on using.

If you want to see them, click on column I (the name box above will show I1:I65536 as the range selected) but don't release the mouse button, then move the cursor over to the next grey box to the left of the I, (the name box above will show A1:I65536) then release, then right-click on column I, and choose Show.

Columns A, B & C create lists of numbers to get the pattern of permutations.

Columns D, E & F convert the number patterns to repeating lists of the names.

Column G is blank.

Column H is a counter used by columns D, E & F to get the names.

Column L concatenates the names in columns D, E & F with the separator in cell M2.

Does that help?


Forgot about this thread! I just uncovered the hidden cells and boy is it complicated! I've gone through it slowly and even discovered a "bug". In the permuation row where it said:
=IF(ROW()>$L$1+1;"";D3&$M$2&E3&$M$2&F3),
it should have said:
=IF(ROW()>$L$1+2;"";D3&$M$2&E3&$M$2&F3)

I only noticed because it was giving one less permutation than expected.

I didn't realise how much work must have gone into it - thanks a lot! You must be pretty hardcore! I'm gonna see if anyone else on Wikipedia will find it useful.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Nov 12, 2007 3:04 pm    Post subject: Reply with quote

This is not a suitable job for a spreadsheet. From a relational database you can get the permutation like this:
SELECT X,Y,Z FROM Table1,Table2,Table3
Back to top
View user's profile Send private message
seanspotatobusiness
OOo Enthusiast
OOo Enthusiast


Joined: 31 Oct 2005
Posts: 176

PostPosted: Tue Jan 15, 2008 6:29 am    Post subject: Reply with quote

Villeroy wrote:
This is not a suitable job for a spreadsheet. From a relational database you can get the permutation like this:
SELECT X,Y,Z FROM Table1,Table2,Table3


Would the database cope when only tables 1 and 2 were in use? The spreadsheet needs all three in use at the same time or else doesn't work.
_________________
Win 7; OOo 3.2

Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Tue Jan 15, 2008 7:19 am    Post subject: Reply with quote

Sorry, I'm on the way out again [wife hinting madly]. However, at first glance, this is, to me, a problem for a programming language even like simple BASIC which will then quickly loop through possibilities. It's been a long while for me, but isn't OO's "BASIC" using a similar structure? Anyhow, I'm on the way out the door, so good luck with this.

David.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Jan 15, 2008 9:24 am    Post subject: Reply with quote

username132 wrote:
Villeroy wrote:
This is not a suitable job for a spreadsheet. From a relational database you can get the permutation like this:
SELECT X,Y,Z FROM Table1,Table2,Table3


Would the database cope when only tables 1 and 2 were in use? The spreadsheet needs all three in use at the same time or else doesn't work.

Database with "Table1" having a field "Letters" with values A to F:
Code:

A
B
C
D
E
F

and "Table2" having a field "Numbers" with values 1 to 6:
Code:

1
2
3
4
5
6

The most basic form of a select-query across two fields gives the cartesian product of the two fields (all combinations):
SELECT "Table1"."Letters", "Table2"."Numbers" FROM "Table1", "Table2"
Code:

Letters   Numbers
A   1
A   2
A   3
A   4
A   5
A   6
B   1
B   2
B   3
B   4
B   5
B   6
C   1
C   2
C   3
C   4
C   5
C   6
D   1
D   2
D   3
D   4
D   5
D   6
E   1
E   2
E   3
E   4
E   5
E   6
F   1
F   2
F   3
F   4
F   5
F   6

6x6 = 36 records
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group