Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I needed this myself several times and finally built it.</p> <p>I believe the code scales for any total number of columns and any number of distinct values within columns (e.g. each column can contain any number of values)</p> <p>It assumes all values in each column are unique (if this is not true, you will get duplicate rows)</p> <p>It assumes you want to cross-join output based on whatever cells you have currently selected (make sure you select them all)</p> <p>It assumes you want the output to start one column after the current selection.</p> <p>How it works (briefly): first for each column and for each row: It calculates the number of total rows needed to support all combos in N columns (items in column 1 * items in column 2 ... * items in column N)</p> <p>second for each column: Based on the total combos, and the total combos of the previous columns it calculates two loops.</p> <p>ValueCycles (how many times you have to cycle through all the values in the current column) ValueRepeats (how many times to repeat each value in the column consecutively) </p> <pre><code>Sub sub_CrossJoin() Dim rg_Selection As Range Dim rg_Col As Range Dim rg_Row As Range Dim rg_Cell As Range Dim rg_DestinationCol As Range Dim rg_DestinationCell As Range Dim int_PriorCombos As Long Dim int_TotalCombos As Long Dim int_ValueRowCount As Long Dim int_ValueRepeats As Long Dim int_ValueRepeater As Long Dim int_ValueCycles As Long Dim int_ValueCycler As Long int_TotalCombos = 1 int_PriorCombos = 1 int_ValueRowCount = 0 int_ValueCycler = 0 int_ValueRepeater = 0 Set rg_Selection = Selection Set rg_DestinationCol = rg_Selection.Cells(1, 1) Set rg_DestinationCol = rg_DestinationCol.Offset(0, rg_Selection.Columns.Count) 'get total combos For Each rg_Col In rg_Selection.Columns int_ValueRowCount = 0 For Each rg_Row In rg_Col.Cells If rg_Row.Value = "" Then Exit For End If int_ValueRowCount = int_ValueRowCount + 1 Next rg_Row int_TotalCombos = int_TotalCombos * int_ValueRowCount Next rg_Col int_ValueRowCount = 0 'for each column, calculate the repeats needed for each row value and then populate the destination For Each rg_Col In rg_Selection.Columns int_ValueRowCount = 0 For Each rg_Row In rg_Col.Cells If rg_Row.Value = "" Then Exit For End If int_ValueRowCount = int_ValueRowCount + 1 Next rg_Row int_PriorCombos = int_PriorCombos * int_ValueRowCount int_ValueRepeats = int_TotalCombos / int_PriorCombos int_ValueCycles = (int_TotalCombos / int_ValueRepeats) / int_ValueRowCount int_ValueCycler = 0 int_ValueRepeater = 0 Set rg_DestinationCell = rg_DestinationCol For int_ValueCycler = 1 To int_ValueCycles For Each rg_Row In rg_Col.Cells If rg_Row.Value = "" Then Exit For End If For int_ValueRepeater = 1 To int_ValueRepeats rg_DestinationCell.Value = rg_Row.Value Set rg_DestinationCell = rg_DestinationCell.Offset(1, 0) Next int_ValueRepeater Next rg_Row Next int_ValueCycler Set rg_DestinationCol = rg_DestinationCol.Offset(0, 1) Next rg_Col End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POExcel vba to create every possible combination of a Range
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USspioter
    1. USspioter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload