Search code examples
excelexcel-formulaoffice365

Using VSTACK need to generate an indicator based on which array the values have been picked up from

Given two tables of the following format containing IDs (ID1 and ID2)

enter image description here

enter image description here

I want to use VSTACK to stack the IDs on top of each other as follows:

enter image description here

However, I also want a second "Source" column which identifies which table I picked up the values from. Is there a clean way of doing this with dynamic formulas?

Solution

  • There are quite some ways to work on this, but in terms of readability maybe use EXPAND():

    enter image description here

    Formula in E1:

    =VSTACK(EXPAND(A2:A5,,2,A1),EXPAND(C2:C5,,2,C1))

    Other option(s) could be:

    =VSTACK(IF({1,0},A2:A5,A1),IF({1,0},C2:C5,C1))

    Or, if you open the python interpreter through =PY then you can try:

    pd.concat([xl("A2:A5").assign(a=xl("A1")),xl("C2:C5").assign(a=xl("C1"))]).values

    Which is more tedious, and probably "bad" python, but I just wanted to give it a shot.