Keep record identity and order when reshaping arrays

Align exported fields, distinguish numeric zero from absence and declare the order before flattening or taking rows.

Original worked example
Source ID	Units	Include?
001	12	Yes
002	0	Yes
001	-2	Yes
	999	No

Align fields before stacking

One export stores ID, Team, Units and Note; another stores Units, Note, ID and Team. Choose columns 1,2,3 from the first and 3,4,1 from the second. VSTACK then appends observations with a common field order.

Use original ID cells to build the presence mask. A blank source copied into an array can appear as zero. Filter on record presence rather than on positive units, so returns, zero-unit records and duplicate IDs survive.

=VSTACK(CHOOSECOLS(A2:D5,1,2,3),CHOOSECOLS(A9:D12,3,4,1))

Choose a scan and a type policy

TOCOL scans across rows by default. TRUE in its third argument scans down each column instead. Both produce a column, but they can place the same observations in different positions.

Ignoring blanks does not remove text numbers or Boolean cells. For a numeric readings list, filter the flattened values with ISNUMBER. Keep numeric zero and negative values. Flattening without labels drops the original batch and sensor coordinates.

=LET(items,TOCOL(B2:D5,1,F2),FILTER(items,ISNUMBER(items)))

Filter before taking the last records

In the recent-record task, source positions are already oldest to newest. Remove empty-ID slots first, reorder the report fields, then use a negative TAKE row count to keep the final active records.

The retained rows keep their original order. A request larger than the active population returns all available rows. Handle an entirely empty population before FILTER so the result has the declared No records state.

=LET(records,FILTER(A2:D9,A2:A9<>""),TAKE(CHOOSECOLS(records,1,4,2),-F2))

Check the changing shape

Keep the reserved spill area clear. Check both the values and the actual output extent, including the final active row and the empty space after a shorter result.

The three exercises retain eight sources each, with changed order, duplicate IDs, zeroes, empty populations and full output capacity. Use those sources to test the rule rather than type the demonstration answers as constants.

Check the idea

An export slot has ID 002 and numeric Units 0. Should the combined record report keep it?

Try the idea

References

Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.