Add the table name to a column using VSTACK.

[Geir Hogstad]

Hi,

When I combine multiple tables in Excel using Vstack, is there an easy way to add the table name to the dynamic list? All tables have the same structure.

One workaround is to add a new column to eache table, sith the product name. But is it possible to grab/extract it from the table name? 

Best regards

Geir

[#2 · Accepted solution]

@Geir Hogstad I don't know of a way to dynamically grab the Table Name but given a Table Name you can easily ADD that column using EXPAND():

=VSTACK(EXPAND(t_appelsin,,4,"t_appelsin"),EXPAND(t_plommer,,4,"t_plommer"),EXPAND(t_pærer,,4,"t_pærer"))

[#3]

Nice, thank you for the tips.

/Geir

[#4]

@Geir Hogstad 

You can create a simple LAMBDA to add the table name to any column, thought it does use INDIRECT, so may have performance impacts on workbooks with many calculations or large datasets:

ADDTABLENAMECOLUMN = LAMBDA(tableName,
    LET(
        tbl, INDIRECT(tableName), 
        EXPAND(tbl, , COLUMNS(tbl) + 1, tableName)
    )
);

This works because we can pass the string version of the table name to INDIRECT to return a reference to the Table. 

We can then use it in your formula, like this:

=VSTACK(
    ADDTABLENAMECOLUMN("t_appelsin"), 
    ADDTABLENAMECOLUMN("t_plommer"), 
    ADDTABLENAMECOLUMN("t_pærer")
)

[#5]

Thank you for your tip.

[#6]

@Geir Hogstad 

 How about this?

http://e.anyoupin.cn/EData/?p=tools.consolidate.consolidateshts/consolidateSheetsSkipRows

[#7]

@Geir Hogstad Inspired by the earlier answers, the attached file contains a dynamic solution that does not require hard-coding of the table names in the EXPAND or VSTACK functions.

Though you need to add two formulas on the sheets that contain the tables you want to stack. In my example I added in A4 a formula like:

=tablename[@Måned]

Hide column A if you don't want to see this formula!

Then in B1 of every sheet I pick-up the table name with:

=MID(FORMULATEXT(A4),2,FIND("[",FORMULATEXT(A4))-2)

Hide row 1 if you don't want to see the table name!

The stacking formula then looks like this:

=VSTACK(EXPAND(t_appelsin,,4,Appelsiner!B1),EXPAND(t_plommer,,4,Plommer!B1),EXPAND(t_pærer,,4,Pærer!B1))

Change the sheet names and/or table names, and all will be updated dynamically. Though you need to press F9 to see the changes.

[#8]

Very cool soultion. Thank you.