Formula to get all possible combinations of 2 separate cells? Includes Screenshot of the issue

[marshen]

Hi all.
This forum has provided me with great assistance in the past and hopefully you can help me today.

I have a sheet that has 90 rows and I want to add a formula to it that will give me all the possible combinations of the 2 cells on the corresponding row, for each of the 90 rows.

Below is an example....

The values are that require the combinations to be calculated are in columns B & C

The possible combinations in my example are in columns D-I, however I'd like a formula to populate these combinations for me, which IO can then just drag down for each of the 90 rows.

It's worth nothing that some of the cells are blank and therefore I'd like the result as shown below, ie, the only possible combination is HH.

On a final note, the maximum characters in either column is 9, which of course will returns many different combinations of the 2 columns in that particular row.

I hope this all makes sense and you can assist me.

Thanks again.

Mark

 

[Legacy 143009]

Are you OK with VBA?

 

[Legacy 143009]

Sub GetString()
  Dim xStr As String
  Dim FRow As Long, i As Long
  Dim xScreen As Boolean
  
  Application.ScreenUpdating = False
  lRow = Cells(Rows.count, 2).End(xlUp).Row
    
  For i = 1 To lRow
    xStr = Cells(i, 2).Value & Cells(i, 3).Value
      If Len(xStr) < 2 Then Exit Sub
      If Len(xStr) >= 8 Then
        MsgBox "Too many permutations!", vbInformation, "Permutation"
        Exit Sub
      Else
        ActiveSheet.Columns(1).Clear
        FRow = 1
        Call GetPermutation("", xStr, FRow, i)
      End If
    Next
    Application.ScreenUpdating = Ture
End Sub
Sub GetPermutation(Str1 As String, Str2 As String, ByRef xRow As Long, r As Long)
  Dim i As Integer, xLen As Integer
  Dim valExist As Boolean
  xLen = Len(Str2)
  If xLen < 2 Then
    valExist = False
    For c = 4 To xRow + 2
      If Cells(r, c).Value = Str1 & Str2 Then valExist = True
    Next
    If Not valExist Then
      Cells(r, xRow + 3).Value = Str1 & Str2
      xRow = xRow + 1
    End If
  Else
    For i = 1 To xLen
      Call GetPermutation(Str1 + Mid(Str2, i, 1), Left(Str2, i - 1) + Right(Str2, xLen - i), xRow, r)
    Next
  End If
End Sub

 

[marshen]

Sub GetString()
  Dim xStr As String
  Dim FRow As Long, i As Long
  Dim xScreen As Boolean
 
  Application.ScreenUpdating = False
  lRow = Cells(Rows.count, 2).End(xlUp).Row
   
  For i = 1 To lRow
    xStr = Cells(i, 2).Value & Cells(i, 3).Value
      If Len(xStr) < 2 Then Exit Sub
      If Len(xStr) >= 8 Then
        MsgBox "Too many permutations!", vbInformation, "Permutation"
        Exit Sub
      Else
        ActiveSheet.Columns(1).Clear
        FRow = 1
        Call GetPermutation("", xStr, FRow, i)
      End If
    Next
    Application.ScreenUpdating = Ture
End Sub
Sub GetPermutation(Str1 As String, Str2 As String, ByRef xRow As Long, r As Long)
  Dim i As Integer, xLen As Integer
  Dim valExist As Boolean
  xLen = Len(Str2)
  If xLen < 2 Then
    valExist = False
    For c = 4 To xRow + 2
      If Cells(r, c).Value = Str1 & Str2 Then valExist = True
    Next
    If Not valExist Then
      Cells(r, xRow + 3).Value = Str1 & Str2
      xRow = xRow + 1
    End If
  Else
    For i = 1 To xLen
      Call GetPermutation(Str1 + Mid(Str2, i, 1), Left(Str2, i - 1) + Right(Str2, xLen - i), xRow, r)
    Next
  End If
End Sub

Hi thanks for your reply.
I know how to run macros, however that's about as far as it goes.

I pasted the code in and changed the = Ture to = True, see below

But unfortunately when I un the macro nothing happens

Any advice would be appreciated.

Thanks
Mark

 

[Legacy 143009]

I pasted the code in and changed the = Ture to = True, see below

Yes, it should be True. Sorry for that. I added this in the last minute.

Weird. It was working for me when I put
B1:HH C1:AA
B2:GG C2:HH

Maybe I got your scenario wrong. I was working fine for me for the scenario above.

 

[Legacy 143009]

Oh I think I know what happened I think your screen updating left False when you got the error. Run this function for once seperately and try the previous code again:

Sub test()
  Application.ScreenUpdating = True
End Sub

 

[marshen]

Oh I think I know what happened I think your screen updating left False when you got the error. Run this function for once seperately and try the previous code again:

Sub test()
  Application.ScreenUpdating = True
End Sub

Thanks but no joy

Nothing happens when either macro is ran

Sadly I don't know much about macros
Maybe you have a screenshot of you trying to replicate the scenario?
I suspect it'll be me and not the code!

Thanks
Mark

 

[Legacy 143009]

You should run the code in the same active sheet with the values.

 

[marshen]

Hi thanks for your efforts, it's really appreciated.

I'm thinking i've explained the requirement incorrectly.

This is to work out all the combinations regarding the order of goals scored within a football match. Basically the H means Home Team and the A means Away team.

The requirement is to get all the possible combinations regarding the order the goals are scored.

So for
1-0 the only combination is H
2-0 the only combination is HH
2-1 the combinations are HHA, HAH, AHH
3-0 the only combination is HHH
3-1 the combinations are HHHA, HHAH, HAHH, AHHH

So the above gives an idea of what i'm looking for.

In the 3-1 example the results are broken down as follows

HHHA The home team scores the first 3 goals and then the away team scores the last goal of the match
HHAH The home team scores the first 2 goals of the match, the away team then scores 1 and then the home team scores the last goal of the match
HAHH The home teams scores the first goal of the match, the away team then score the 2nd goal of the match, then the home team go on and score the 3rd and the 4th goal of the match
AHHH The away team score the first goal of the match, then the home team score the next 3 goals

All the above combinations result in a 3-1 result, however the goals could be scored in any of the above orders.

Below are 3 screenshots showing the whole sheet. As you can see when the scores get to 9-8, there will be many possible combinations in regards to the order of the goals, hence the need for a macro or formula to calculate these.

In an ideal world, a formula / macro would populate each possible outcome starting in column D for the corresponding row, so for our 3-1 example and all the scores above, the first screenshot below shows the desired outcome, with all the combinations shown in red.

Below shows the full sheet.

I hope this all makes sense and isn't too difficult.

Thanks
Mark

 

[Legacy 143009]

I still think my code does the thing you are after
I have no orher idea as far as I can understand. Sorry...