Yes, it is possible to do. I even started to develop the formulas . . . then I came to my senses!
Formulas like these are a good way to learn Excel, or as an intellectual exercise, but they are really not suited for genuine work. They are too big, too complicated, and not very flexible. Something like this can be done much better with a macro. I developed one that should do what you want.
1) Open Excel.
2) Press Alt-F11 to open the VBA editor.
3) Select Insert --> module from the menu.
4) Paste in the following code:
Code:
Sub Combos()
Dim Element(), Index()
Dim MyCols As Variant, MySheet As Worksheet, OneCol As Boolean
Dim r As Long, c As Long, ctr As Long, mysize As Long
Dim delim As String, OutputCol As String, str1 As String
' Set up conditions
Set MySheet = Sheets("Sheet1")
MyCols = Array("A", "B", "C")
OutputCol = "F"
OneCol = True
delim = " - "
' resize the arrays
ReDim Element(255, UBound(MyCols))
ReDim Index(UBound(MyCols))
' Read the elements
For c = 0 To UBound(MyCols)
Element(0, c) = 0
Index(c) = 1
For r = 1 To 255
If MySheet.Cells(r, MyCols(c)) <> "" Then
Element(0, c) = Element(0, c) + 1
Element(Element(0, c), c) = MySheet.Cells(r, MyCols(c))
End If
Next r
Next c
' Clear the output columns(s), and check for the number of results
ctr = MySheet.Cells(1, OutputCol).Column
mysize = 1
For c = 0 To UBound(MyCols)
mysize = mysize * Element(0, c)
MySheet.Columns(ctr).ClearContents
ctr = ctr + 1
Next c
If mysize > 1000000 Then
MsgBox "The number of results is too big to handle!"
Exit Sub
End If
ctr = 0
' Start creating combinations
Loop1:
ctr = ctr + 1
str1 = ""
Set resultcell = MySheet.Cells(ctr, OutputCol)
For c = 0 To UBound(MyCols)
If OneCol Then
str1 = str1 & Element(Index(c), c) & delim
Else
resultcell.Value = Element(Index(c), c)
Set resultcell = resultcell.Offset(0, 1)
End If
Next c
If OneCol Then MySheet.Cells(ctr, OutputCol) = Left(str1, Len(str1) - Len(delim))
' Increment the indices
For c = 0 To UBound(MyCols)
Index(c) = Index(c) + 1
If Index(c) <= Element(0, c) Then Exit For
Index(c) = 1
Next c
If c <= UBound(MyCols) Then GoTo Loop1:
End Sub
5) Customize it to your needs. The first 5 executable lines (under 'Set up conditions") allow you to select which columns have data, where you want to put the results, and if you want the results to be in one column or multiple columns.
6) Return to Excel.
7) Set up your data. An empty cell, or one that has "" in it from a formula will be ignored.
8) Press Alt-F8, select Combos, the click Run.
That should do it. Good luck!
