Mixing Three Lists To Give All Permuations? [SOLVED]

[seanspotatobusiness]

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?

[Metalrat]

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

[RickRandom]

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.

[seanspotatobusiness]

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?

[RickRandom]

[RickRandom]

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

http://www.mytempdir.com/1197593

[seanspotatobusiness]

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!

[RickRandom]

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?

[seanspotatobusiness]

[Villeroy]

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

[seanspotatobusiness]

[David]

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.

[Villeroy]