Course / Modern Excel

Combine lists: VSTACK and HSTACK

Stack two months of orders into one list with VSTACK, find every customer across both with UNIQUE, and add a total beside each with HSTACK. Intermediate · 5 min · VSTACK, HSTACK, Spill references (#), UNIQUE across ranges

Data often arrives in pieces: one block per month, per region, per sheet. Two functions put the pieces together with a formula, so the combined list stays live:

Here, a bakery's orders for January and February sit in separate blocks.

Steps

  1. Put both months in one list. In G2, stack February under January: =VSTACK(A2:B5, D2:E5)

  2. Who ordered at all, in either month? In J2, stack the two customer columns and remove the repeats: =UNIQUE(VSTACK(A2:A5, D2:D5))

  3. Finally, a summary with each customer and what they spent in total. In L2, put the customer list from J2 beside a SUMIF of the stacked orders: =HSTACK(J2#, SUMIF(G2:G9, J2#, H2:H9))

Remember

In the function reference: HSTACK, SUMIF, UNIQUE, VSTACK

Previous: Reshape a list: TAKE, DROP and CHOOSECOLS · Next: Your own calculations: LAMBDA, BYROW and MAP