Multi-Dimension Data Table using PyXLL (with video) Dec 2020

MD Data Table in Action — Video

3 inputs; 2 outputs

Bonus — Video

Merged my data table values into Excel’s data table

3D Hypotenuse

Screenshots

Independent Variables — Input

qvar_0 (10 items)

qvar_1 ( 7 items)

qvar_2 ( 4 items)

Dependent Variables — Results

Dependent Variables — Details (only for clarity)

Short Tabulation

Long Tabulation

(10 x 7 x 4 = 280 items)

More Details

Selecting Results Cells

Selecting Anchor for Tabulation

Added August 2, 2021

Python code snippets — using PyXLL

A visitor asked if I would share the code. I will share a few Python snippets. My version was written for use with Python and PyXLL. I presume something like it could be written using some other Python-interface-to-Excel products such as openpyxl (although I have not used openpyxl).

..
..
from pyxll import xl_macro
..
..
#my function is defined as a PyXLL macro
@xl_macro()
def sense():
    """Create a multi-dimension data table alternative for Excel."""
    ..
    
    # get the Excel application object from PyXLL
    ex = _xl_app()
    ..
    
    # example -- getting lists
    list0 = [e.Value for e in ex.Range("qvar_0")]
    list1 = [e.Value for e in ex.Range("qvar_1")]
    list2 = [e.Value for e in ex.Range("qvar_2")]
    # et cetera
    ..

    # example -- gathering lists
    ss = [list0, list1, list2, list3, list4]
    ..
    
    # example -- producing all possible combinations of elements in lists in s 
    range_result = []
    for en, e in enumerate(list(itertools.product(*ss)), start=1):
    ..
    
    # then, in a loop, for all the possible combinations -- substitute, calculate, repeat
    # use ex.Calculate()
    ..