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

Data Table
I coded a PyXLL macro that builds a multi-dimension data table.

The native Data Table provided by Excel is useful but limited to two variables.

My version is set up for 5 independent variables and an unlimited (theoretically) number of dependent variables. 5 was an arbitrary choice; some limitation is required here.

What is a native Data Table?

https://support.microsoft.com/en-us/office/calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b

MD Data Table in Action — Video

3 inputs; 2 outputs

Bonus — Video

Merged my data table values into Excel’s data table

3D Hypotenuse

3d Hypotenuse

Screenshots

Independent Variables

Independent Variables — Input

qvar_0 (10 items)

qvar_1 ( 7 items)

qvar_2 ( 4 items)

Dependent Variables

Dependent Variables — Results

Dependent Variables Details

Dependent Variables — Details (only for clarity)

Short Tabulation

Short Tabulation

Long Tabulation

Long Tabulation

(10 x 7 x 4 = 280 items)

More Details

Selecting Results Cells

Selecting Results Cells

Selecting Anchor for Tabulation

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 use 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()
    ..

2 thoughts on “Multi-Dimension Data Table using PyXLL (with video) Dec 2020

    1. Thank you for the comment. I would rather share only snippets of code. I appended a short section labeled in red “Added August 2, 2021”

Leave a Reply

Your email address will not be published. Required fields are marked *