using index table and automated sentence structuring table to output variable sentences and paragraphs

[bair004]

I am attempting to develop a sentence structuring template in Excel that will automate sentence and paragraph creation, combining cells and factoring (indexing?) variables, using two sets of data.

Before I go further, here is the source of my inspiration, including steps, and formulas. I just can't figure out how to make it work! "How I Wrote 500,000 Unique GoogleBase Descriptions in 2 Hours" link - http://www.seerinteractive.<wbr>com/blog/how-i-wrote-500000-<wbr>unique-googlebase-<wbr>descriptions-in-2-hours

For those of you who don't want to click that link and read through a whole page of instructions, I will attempt to simplify my "question", as follows:

Below is the suggested formula provided by the author/source from the above link. This formula gives me an error when i copy and execute in a cell in Excel. An open dialog popup appears when I attempt to execute the formula... so I tried copying data set 1 (with about 30 rows of data) and then opening that file when the dialog appears. It did produce a paragraph in the cell, but just one paragraph, not ordered permutations / combinations which is the desired result. I'm sorry to say that i was unable to figure out what went wrong.

Here's the formula.. note that the author / source breaks this formula down step by step, but does not explain much about how it works... at least it did not help me.

=SUBSTITUTE(SUBSTITUTE(<wbr>CONCATENATE((INDEX(Sentences!$<wbr>C$4:$G$4,1,TRUNC((5*RAND())+1)<wbr>))," ",(INDEX(Sentences!$C$6:$G$6,<wbr>1,TRUNC((5*RAND())+1)))," ",(INDEX(Sentences!$C$5:$G$5,<wbr>1,TRUNC((5*RAND())+1)))," ",(CONCATENATE("The product fits the following themes: ",C2,", ",D2," and ",E2,". "))," ",(INDEX(Sentences!$H$5:$L$5,<wbr>1,TRUNC((5*RAND())+1)))," ",(CONCATENATE("Manufactured by ",F2,". ")),(CONCATENATE("[Product] comprised of ",G2,". ")),(CONCATENATE("Available in ",H2,". ")),(INDEX(Sentences!$C$7:$G$<wbr>7,1,TRUNC((5*RAND())+1)))),"[<wbr>Product]",A2),"[Category]",B2)

So my question is sort of simple, but obviously quite complex for me: How do I make this formula execute permutations of Data Set 2, IN SENTENCE STRUCTURE (column) ORDER, (not by all combinations - that would create disorderly sentences obviously) and also factor in the appropriate attributes of Data Set 1, so that it outputs thousands of unique sentences and paragraphs using the index table values that are plugged in the approprate places on the sentence structure template /table?

I sure hope that made sense. If not, you can click the above link or search "How I Wrote 500,000 Unique GoogleBase Descriptions in 2 Hours" and you will find the source document.

Thank you!

 

[Andrew Poulsom]

Welcome to MrExcel.

Do you have a worksheet named Sentences? If not you will get the Update Values dialog.

 

[bair004]

Hi Andrew, Thank you. I did not have the "sentences" worksheet and when I added the sheet to the same workbook, I still got an error. When i created a new "Sentences" workbook, excel ran the formula, but now the sentences are structured in the wrong output order. The sentence structure worksheet that I created is inserted here:

[/IMG]

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

And this is the index sheet that I am using for demo purposes to input the [] attribute variables, in this case product attributes, keywords, features, benefits, etc.:

[/IMG]

As you can see above, I'm getting a very peculiar error box message "?Required"... not sure what that's about. Also, the sentences are not structured in the desired order. The first image above shows the correct ordered layout... first column is opening segment of a complete sentence... and that should flow with the second column (second segment of the sentence), and so on...

Also, instead of using the RAND function, I would like to output the results in order... starting with the first cell in the sequence... and then an iteration of the first row... then remain in the first cell (sentence opening), and adding the ordered segments from the 2nd row, and so on, until it completes the sequence through to the last row. Then it should loop back to the first row again, but this time, only combining the first two cells in the first row, then complete the sentence from the remaining ordered cells in the 2nd row, producing an entirely different iteration, and so on, continuing the loop until all "ordered" permutations are output, in sequential order, by cell, row and column, in that order from top left, to bottom right of the matrix... not a RAND... not really a permutation, but a logical ordered sequence.

Lastly, I would like the index (product attributes) variables to also be inputted in sequential order, in the same manner described above. Each time the excel formula calls for a (Product), (Category), (Keyword), etc. to be inputted into the output sentences, they should be in order, so that our editorial team can work with the content paragraphs in blocks, one product at a time. If my estimation is correct, for example, the first product should produce several hundred paragraphs, which ideally would be inputted into different sheets, by product category.

I am very excited about completing this project. This is absolutely not about creating artificial content, but using automated language processing tailored to our products and our market, to create, as the original article stated, several hundred thousand paragraphs (in our case, we have 8000 products to write copy for), and thereby reducing the workload of our editorial team. Excel is truly an amazing tool.

Thank you for making this forum available... and I am happy to support your forum and help in any way that i can.