Combination Generator yet keep in order

Question

Wondering if anyone could help me. I'm stumped. It's been ages since I used excel....

I have 9 columns with different values in each cell, different numbers of cells per column.

I need a formula/macro to spit out all combinations of the cells and yet still remain in the exact same order of the columns.

For example Columns:

D   / 003 / 23  / 3 / 3R  / C  / VFX

... / 005 / 48  / 3 / 12  / .. / VDF

... / 007 / ... / 1 / ... /... / HSF

And it spits out like this:

D0032333RCVFX

D0032333RCVDF

D0032333RCHSF

D0034833RCVFX

D0034833RCVDF

and so on.... and so on.....

Answer 1

Presumably you will want to call this function with a "serial number" - so that you can call "the Nth combination". The problem then breaks into two parts:

Part 1: figure out, for a given "serial number", which element of each column you need. If you had the same number of elements E in each column it would be simple: it's like writing N in base E. When the number of elements in each column is different, it's a little bit trickier - something like this:

Option Base 1
Option Explicit

Function combinationNo(r As Range, serialNumber As Integer)
' find the number of entries in each column in range r
' and pick the Nth combination - where serialNumber = 0 
' gives the top row
' assumes not all columns are same length
' but are filled starting with the first row

Dim ePerRow()
Dim columnIndex As Integer
Dim totalElements As Integer
Dim i, col
Dim tempString As String

ReDim ePerRow(r.Columns.Count)
totalElements = 1
i = 0
For Each col In r.Columns
  i = i + 1
  ePerRow(i) = Application.WorksheetFunction.CountA(col)
 totalElements = totalElements * ePerRow(i)
Next

If serialNumber >= totalElements Then
  combinationNo = "Serial number too large"
  Exit Function
End If

tempString = ""
For i = 1 To UBound(ePerRow)
  totalElements = totalElements / ePerRow(i)
  columnIndex = Int(serialNumber / totalElements)
  tempString = tempString & r.Cells(columnIndex + 1, i).Value
  serialNumber = serialNumber - columnIndex * totalElements
Next i

combinationNo = tempString

End Function

You call this function with the range where your columns are, and a serial number (starting at 0 for "top row only"). It assumes that any blank space is at the bottom of each column. Otherwise, it will return a string that is the concatenation of combinations of values in each column, just as you described.

EDIT perhaps the following picture, which shows how this is used and what it actually does, helps. Note that the first reference (to the table of columns of different length) is an absolute reference (using the $ sign, so when you copy it from one cell to another, it keeps referring to the same range) while the second parameter is relative (so it points to 0, 1, 2, 3 etc in turn).