How to rearrange/create variations of text?

[excelrookie1]

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

 

[cyrilbrd]

Hi, you could use vba code

Dim CurrentRow

Sub GetString()
    Dim InString As String
    InString = InputBox("Enter text to permute:")
    If Len(InString) < 2 Then Exit Sub
    If Len(InString) >= 8 Then
        MsgBox "Too many permutations!"
        Exit Sub
    Else
        ActiveSheet.Columns(1).Clear
        CurrentRow = 1
        Call GetPermutation("", InString)
    End If
End Sub

Sub GetPermutation(x As String, y As String)
'   The source of this algorithm is unknown
    Dim i As Integer, j As Integer
    j = Len(y)
    If j < 2 Then
        Cells(CurrentRow, 1) = x & y
        CurrentRow = CurrentRow + 1
    Else
        For i = 1 To j
            Call GetPermutation(x + Mid(y, i, 1), _
            Left(y, i - 1) + Right(y, j - i))
        Next
    End If
End Sub

Then run vba 'GetString'
type 123 instead of the words.
you get in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

123

132

213

231

312

321

<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>
</tbody>

Use Find /Replace to replace the integers by the words. Would that work for you?

 

[hiker95]

excelrookie1,

Welcome to the MrExcel forum.

Here is a solution, but, with a different starting area/range, and, output area.

Sample raw data:

Excel 2007
	A	B	C	D	E
1	squeezy	3
2	honey
3	clear
4
5
6
7
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E
1	squeezy	3	squeezy	honey	clear
2	honey		squeezy	clear	honey
3	clear		honey	squeezy	clear
4			honey	clear	squeezy
5			clear	squeezy	honey
6			clear	honey	squeezy
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Permutations_pgc01()
' pgc01
' http://www.mrexcel.com/forum/showthread.php?t=412952
Dim rRng As Range, p
Dim vElements, lRow As Long, vresult As Variant
 
Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of values
p = Range("B1").Value ' How many are picked
 
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Application.ScreenUpdating = False
Call PermutationsNP(vElements, CInt(p), vresult, lRow, 1)
Application.ScreenUpdating = True
End Sub
 
Sub PermutationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iIndex As Integer)
Dim i As Long, j As Long, bSkip As Boolean
 
For i = 1 To UBound(vElements)
  bSkip = False
  For j = 1 To iIndex - 1
    If vresult(j) = vElements(i) Then
      bSkip = True
      Exit For
    End If
  Next j
  If Not bSkip Then
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
      lRow = lRow + 1
      Range("C" & lRow).Resize(, p) = vresult
    Else
      Call PermutationsNP(vElements, p, vresult, lRow, iIndex + 1)
    End If
  End If
Next i
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Permutations_pgc01 macro.

 

[hiker95]

excelrookie1,

I was not able to modify the Permutations_pgc01 macro, or the PermutationsNP macro to truly work with any string length. Because this is a nice challenge, I will try to come up with some code that will work with any length string of words that are separated by a space character.


If you are always working with a string in cell A1, like squeezy clear honey, where there are three words separated by a single space character, then:.

Sample raw data:

Excel 2007
	A	B
1	squeezy clear honey
2
3
4
5
6
7
Sheet1

After the macro:

Excel 2007
	A	B
1	squeezy clear honey	squeezy clear honey
2		squeezy honey clear
3		clear squeezy honey
4		clear honey squeezy
5		honey squeezy clear
6		honey clear squeezy
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgData()
' hiker95, 01/04/2014
' http://www.mrexcel.com/forum/excel-questions/748064-how-rearrange-create-variations-text.html
Dim s, b As Variant
Columns(2).ClearContents
s = Split(Trim(Range("A1")), " ")
ReDim b(1 To 6, 1 To 1)
b(1, 1) = s(0) & " " & s(1) & " " & s(2)
b(2, 1) = s(0) & " " & s(2) & " " & s(1)
b(3, 1) = s(1) & " " & s(0) & " " & s(2)
b(4, 1) = s(1) & " " & s(2) & " " & s(0)
b(5, 1) = s(2) & " " & s(0) & " " & s(1)
b(6, 1) = s(2) & " " & s(1) & " " & s(0)
Range("B1").Resize(UBound(b, 1), UBound(b, 2)) = b
Columns(2).AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[hiker95]

excelrookie1,

Thank you pgc01.

I have tested the new macros with a string like squeezy clear honey pgc01 hiker95 and, it created a list of 120 rows.

Sample raw data:

Excel 2007
	A	B
1	squeezy clear honey
2
3
4
5
6
7
Sheet1

After the macro:

Excel 2007
	A	B
1	squeezy clear honey	squeezy clear honey
2		squeezy honey clear
3		clear squeezy honey
4		clear honey squeezy
5		honey squeezy clear
6		honey clear squeezy
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Permutations_hiker95()
' hiker95, 01/04/2014
' http://www.mrexcel.com/forum/excel-questions/748064-how-rearrange-create-variations-text.html
' Original macro code by pgc01 has been modified
'Sub Permutations_pgc01()
' pgc01
' http://www.mrexcel.com/forum/showthread.php?t=412952
Dim s, i As Long
Dim rRng As Range, p
Dim vElements, lRow As Long, vresult As Variant
s = Split(Trim(Range("A1")), " ")
p = UBound(s) + 1
vElements = Application.Index(s, 1, 0)
ReDim vresult(1 To p)
Application.ScreenUpdating = False
Call PermutationsNP_V2(vElements, CInt(p), vresult, lRow, 1)
Columns("A:B").AutoFit
Application.ScreenUpdating = True
End Sub
 
Sub PermutationsNP_V2(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iIndex As Integer)
' hiker95, 01/04/2014
' http://www.mrexcel.com/forum/excel-questions/748064-how-rearrange-create-variations-text.html
' Original macro code by pgc01 has been modified
'Sub PermutationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iIndex As Integer)
' pgc01
' http://www.mrexcel.com/forum/showthread.php?t=412952
Dim i As Long, j As Long, bSkip As Boolean
For i = 1 To UBound(vElements)
  bSkip = False
  For j = 1 To iIndex - 1
    If vresult(j) = vElements(i) Then
      bSkip = True
      Exit For
    End If
  Next j
  If Not bSkip Then
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
      lRow = lRow + 1
      Range("B" & lRow) = Join(vresult, " ")
    Else
      Call PermutationsNP_V2(vElements, p, vresult, lRow, iIndex + 1)
    End If
  End If
Next i
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Permutations_hiker95 macro.

 

[hiker95]

excelrookie1, or should I say excelrookie123,

Cross posted here:
How to rearrange/create variations of text?

A message to forum cross posters, please read the following link:
Excelguru Help Site - A message to forum cross posters

 

[cyrilbrd]

...I have tested the new macros with a string like squeezy clear honey pgc01 hiker95 and, it created a list of 120 rows...

Thanks for the vba. Is there any limit to the number of variables?

 

[hiker95]

cyrilbrd,

Is there any limit to the number of variables?

I do not know. I only tested it with a string containing two, three, and, five string variables squeezy clear honey pgc01 hiker95

Test it and get back to us with the results.

Thanks for the vba.

You are very welcome.