How to rearrange/create variations of text?

Hi there,

I need to create variations of text in Excel.

For e.g. in cell A1 I have the text: 'squeezy clear honey'. And I need to create variations of this text in column B.

I can create this manually by typing out all the variations.

For example:

squeezy honey clear
clear squeezy honey
clear honey squeezy
honey clear squeezy
honey squeezy clear

Is there a way to automatize this process instead of manually typing out the variations?

This will save me 10+ hours.

Many thanks

Based on the excellent code by PGC01 here:
http://www.mrexcel.com/forum/excel-q...mutations.html

I think this will take a list of words in A1 and give you all possible permutations:

Please Login or Register  to view this content.

Thank you Yudlugar! That works for one entry in A1.

But we have thousands of entries in column A.

E.g.
squeezy clear honey (A1)
red hot chilli peppers (A2)
large delicious ripe juicy apples (A3)
dried apricots (A4)

What do we do in order to create all of the permutations into column B?

Many thanks

You could build a loop like this...

Please Login or Register  to view this content.

It might still take 10 hours though!

Thank you Yudlugar!

The loop works.

To be more specific there are two more columns which need to align with the results:

Here is the data we have (albeit thousands more rows):

Main Group	Sub Group	Keyword (without variation)
apples	red apples	apples red
apples	big green apples	big green apples
bananas	yellow bananas	yellow bananas
bananas	big riped yellow bananas	riped yellow bananas big

Here is what the results should look like:

Main Group	Sub Group	Keyword (with variations)
apples	red apples	red apples
apples	red apples	apples red
apples	big green apples	big green apples
apples	big green apples	big apples green
apples	big green apples	apples green big
apples	big green apples	apples big green
apples	big green apples	green apples big
apples	big green apples	green big apples
bananas	yellow bananas	yellow bananas
bananas	yellow bananas	bananas yellow
bananas	big riped yellow bananas	big riped yellow bananas
bananas	big riped yellow bananas	big riped bananas yellow
bananas	big riped yellow bananas	big yellow riped bananas
bananas	big riped yellow bananas	big yellow bananas riped
bananas	big riped yellow bananas	big bananas riped yellow
bananas	big riped yellow bananas	big bananas yellow riped
bananas	big riped yellow bananas	riped big yellow bananas
bananas	big riped yellow bananas	riped big bananas yellow
bananas	big riped yellow bananas	riped yellow big bananas
bananas	big riped yellow bananas	riped yellow bananas big
bananas	big riped yellow bananas	riped bananas big yellow
bananas	big riped yellow bananas	riped bananas yellow big
bananas	big riped yellow bananas	yellow big riped bananas
bananas	big riped yellow bananas	yellow big bananas riped
bananas	big riped yellow bananas	yellow riped big bananas
bananas	big riped yellow bananas	yellow riped bananas big
bananas	big riped yellow bananas	yellow bananas big riped
bananas	big riped yellow bananas	yellow bananas riped big
bananas	big riped yellow bananas	bananas big riped yellow
bananas	big riped yellow bananas	bananas big yellow riped
bananas	big riped yellow bananas	bananas riped big yellow
bananas	big riped yellow bananas	bananas riped yellow big
bananas	big riped yellow bananas	bananas yellow big riped
bananas	big riped yellow bananas	bananas yellow riped big

What do we do in order to create the second table?

Many thanks

Try the code below,

Note - a couple of tips that will help you get good a quick answers:
1. Rather than typing out a table into the post, make an example worksheet (click go advanced and then attachments to attach it to your post). This will make it much more likely that you will get a response as it saves people from typing out all your data. It also helps avoid problems due to data formatting
2. Describe the whole problem in the first post, rather than making a problem like the one you have and then expanding it, the best thing to do is to add a sheet in to your example workbook that gives an example of the solution that you want. I've edited the code I originally wrote to cope with the modifications but it may be that it would have been better to code it another way, had I known that was what things were like from the start!

Please Login or Register  to view this content.

Thank you Yudlugar

You are a legend.

Thank you for the tips - I will bear that in mind next time I ask a question.

Thanks again