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:
VSTACK(a, b, …)puts ranges one under anotherHSTACK(a, b, …)puts them side by side
Here, a bakery's orders for January and February sit in separate blocks.
Steps
Put both months in one list. In
G2, stack February under January:=VSTACK(A2:B5, D2:E5)Who ordered at all, in either month? In
J2, stack the two customer columns and remove the repeats:=UNIQUE(VSTACK(A2:A5, D2:D5))Finally, a summary with each customer and what they spent in total. In
L2, put the customer list fromJ2beside a SUMIF of the stacked orders:=HSTACK(J2#, SUMIF(G2:G9, J2#, H2:H9))
Remember
VSTACK(a, b)stacks ranges one under another;HSTACK(a, b)puts them side by side.UNIQUE(VSTACK(…))finds the distinct values across several ranges.J2#refers to everything that spills from J2.- A stacked list is live: it changes when any of its pieces does.
In the function reference: HSTACK, SUMIF, UNIQUE, VSTACK
Previous: Reshape a list: TAKE, DROP and CHOOSECOLS · Next: Your own calculations: LAMBDA, BYROW and MAP