Generate All Possible Permutations...

ellison

Active Member
Joined
Aug 1, 2012
Messages
337
Office Version
  1. 365
Platform
  1. Windows
Hi, we are trying to tidy up some data & have hit a brick wall!

Is it possible to:
i) generate all possible permutations from different strings of text that are in a single cell (separated by ";")?
ii) each permutation must include "2" of the possible strings of text (even if it's a duplicate)
iii) somehow keep track of "which permutation" it came from (either by Excel putting the different outcomes on the same row as below OR if a new sheet is generated for the results, keeping Column A as the Line ID and Column B being the outcome)

Happy to use functions or VBA

Hopefully some sample data may explain what we are trying to say a bit better!!!

Line IDPossible CombinationOutcome1Outcome2Outcome3Outcome4Outcome5Outcome6Outcome7Outcome8Outcome9
Permutation1whitewhite / white
Permutation2white;blackwhite / whitewhite / blackblack / whiteblack / black
Permutation3white;black;redwhite / whitewhite / blackwhite / redblack / whiteblack / blackblack / redred / whitered / blackred / red

Happy to (try to!) answer any questions

We are totally stumped, any help greatly appreciated!
 

Attachments

  • 1574941564102.png
    1574941564102.png
    24.6 KB · Views: 9

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
How about
VBA Code:
Sub ellison()
    Dim Cl As Range
    Dim Sp As Variant
    Dim i As Long, j As Long, k As Long
   
    For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
        Sp = Split(Cl.Value, ";")
        For i = 0 To UBound(Sp)
            For j = 0 To UBound(Sp)
                k = k + 1
                Cl.Offset(, k) = Sp(i) & " / " & Sp(j)
            Next j
        Next i
        k = 0
    Next Cl
End Sub
 
Last edited:
Upvote 0
Code:
    Next j
Next i
I am not sure that OP solve it himeself
 
Upvote 0
Oops, Thanks for that @KOKOSEK I've change post#2 so that it's now correct.
 
Upvote 0
Hi, huge thanks for the code....
Somehow, I'm getting an error message. It's probably something that I have done at this end....!??
I created a macro enabled excel file & inserted the code (using Alt F11, insert module)
Then my raw data is as per the thumbnail: raw_file_excel.png
When I try to execute the macro, I get the error message come up as per the other thumbnail: error-message.png

Huge apologies if it's something I'm doing at this end!

Any chance of a pointer?!

Best
 

Attachments

  • error-message.png
    error-message.png
    107.6 KB · Views: 7
  • raw_file_excel.png
    raw_file_excel.png
    21.7 KB · Views: 7
Upvote 0
oh crumbs, ignore this...……. I've just seen that you changed the code!
Will give this a go and report back in
Wow, it's all go here!!!
 
Upvote 0
Absolutely wonderful, as ever - huge thanks to mrexcel and to Mr Fluff in particular!!!!!
 
Upvote 0
You're welcome & thanks for the feedback.
Also thanks to @KOKOSEK for spotting my mistake (y)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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