How to rearrange/create variations of text?

excelrookie1

New Member
Joined
Jan 3, 2014
Messages
1
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi, you could use vba code
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?
 
Upvote 0
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
ABCDE
1squeezy3
2honey
3clear
4
5
6
7
Sheet1


After the macro:


Excel 2007
ABCDE
1squeezy3squeezyhoneyclear
2honeysqueezyclearhoney
3clearhoneysqueezyclear
4honeyclearsqueezy
5clearsqueezyhoney
6clearhoneysqueezy
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.

Code:
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.
 
Last edited:
Upvote 0
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
AB
1squeezy clear honey
2
3
4
5
6
7
Sheet1


After the macro:


Excel 2007
AB
1squeezy clear honeysqueezy clear honey
2squeezy honey clear
3clear squeezy honey
4clear honey squeezy
5honey squeezy clear
6honey 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).

Code:
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.
 
Last edited:
Upvote 0
ADVERTISEMENT
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
AB
1squeezy clear honey
2
3
4
5
6
7
Sheet1


After the macro:


Excel 2007
AB
1squeezy clear honeysqueezy clear honey
2squeezy honey clear
3clear squeezy honey
4clear honey squeezy
5honey squeezy clear
6honey 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).

Code:
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.
 
Upvote 0
...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?
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,196,447
Messages
6,015,321
Members
441,889
Latest member
balolaptopgiaolong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top