On Sat, 2007-03-17 at 16:07 +0000, Paul Smith wrote: > Dear All > > I have a long spreadsheet as follows: > > X1 X2 X3 X4 X5 > 2 6 1 1 4 > 2 1 3 1 5 > 3 3 3 4 6 > 4 2 5 3 4 > 6 6 5 1 1 > 5 6 4 1 3 > > [...] > > For each number and for each X variable, there is a small piece of > text. I am looking for a program to replace, for each spreadsheet line > and for each number therein, the respective text and create a phrase > concatenating the pieces of text corresponding to the numbers. Is > there some tool to accomplish this goal? > > Thanks in advance, > > Paul > Hi, Paul, I assume that the text is also in your spreadsheet. So you actually have something like: X1 X2 X3 X4 X5 TEXT 2 6 1 1 4 abc 2 1 3 1 5 def 3 3 3 4 6 ghi 4 2 5 3 4 jkl 6 6 5 1 1 mno 5 6 4 1 3 pqr You want something to come out somewhere that would be a phrase consisting of text(2) text(6) text(1) text(1) text(4) so, you want to index the text column from the data at each row,column pair. Now create a new sheet in the same workbook. In this sheet, have it do something like: =INDIRECT(sheet1.text,index(sheet1.text,X1,2)) You will need to refer to the help pages in open office to get a more refined solution. This requires that you have defined the cell column names. If you are attempting to generate some form of Haiku, this will work. If you are attempting a substitution cipher for encryption, be aware that most countries will be very highly upset, and could result in severe consequences for such use in communications. A spreadsheet is probably not the best tool for this type of application. You will quickly find it too difficult to manage the entries, and there will be a severe limitation on the number of words and phrases that will result. You might be interested in perusing some of the language forums in the IEEE archives, or the ACM. There is also a considerable body of work available from Russia on AI that deals with language and language generation. Resolving language is seldom a question of just words. Most human languages use an evolutionary structure that will result in prefix(es)-root(s)-suffix(es) that makes the word choice quite large. Even children of 12 have vocabularies that exceed 32,000 words, and I do not believe that that includes the full prefix/suffix expansion. Additionally our marvelous brains allow us to use language by means of only 70% or so clarity and still produce reasonable results. This is one of the difficulties of language for computers. And this doesn't include the non word verbalizations (Hmmmm?) or non verbal aspects such as body language or facial expression. Add in the criteria of word choice, syntax, environment, currency of the conversation, and it is quite a computing issue. If you would give me more information about your goal, I might be able to point you in a productive direction, but YMMV. Good luck. Regards, Les H
