Stacking the raw exports
The same column position means different fields in the two exports. Align the second block first.
Combine exports with different column orders
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Combine the first export A2:D5 followed by the second export A9:D12. Return ID, Team and Units in that order, with no headings. The second export stores Units, Note, ID and Team. Omit a slot only when its ID is empty. Retain duplicate IDs, source order, signed units and numeric zero. Return No records in one cell when both blocks have no active ID.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:I12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Align ID, Team and Units before stacking two exports, preserving duplicate records and signed quantities.
THE QUICK ANSWER01
Align ID, Team and Units before stacking two exports, preserving duplicate records and signed quantities.
ƒx
=LET(records,VSTACK(CHOOSECOLS(A2:D5,1,2,3),CHOOSECOLS(A9:D12,3,4,1)),keep,VSTACK(A2:A5<>"",C9:C12<>""),FILTER(records,keep,"No records"))| G | H | I |
|---|---|---|
| 001 | East | 12 |
| 002 | West | 0 |
| 001 | East | -2 |
| A* | North | 7 |
| B? | South | -3 |
| 009 | West | 4 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Keep each observation
The first source returns 001/East/12, 002/West/0, 001/East/-2, A*/North/7, B?/South/-3 and 009/West/4. The repeated 001 is a separate record. Empty-ID slots are excluded even if their other cells contain values.
Keep text identity
IDs remain literal text, including leading zeroes, case, wildcard-looking punctuation and punctuation. This task combines observations; it does not deduplicate, reconcile or interpret ID syntax.
Edit within the input contract
Active IDs and teams use nonempty ASCII text up to 40 characters. Active records require numeric whole units within ±1,000,000. An empty-ID slot is excluded regardless of its other values. Keep G5 and the reserved spill area clear.
Read the full step-by-step walkthrough
- Map the first export
Choose ID, Team and Units from columns 1,2,3. Leave Note out of the report.
- Map the second export
This block stores Units, Note, ID and Team. Choose columns 3,4,1 to match the first report layout.
- Stack the aligned records
VSTACK appends the second block after the first. It does not match IDs, remove duplicates or sort records.
- Build the mask from original IDs
Compare the original ID cells with empty text before stacking the mask. A blank copied into an array can display as zero, so read the original ID references.
- Filter by record presence
Use the eight-position ID mask. A zero in Units remains a valid observation because units do not decide whether a record exists.
Things to watch
Filtering on positive units
Zero and returns are valid records. Only an empty ID excludes a slot.
Removing duplicate IDs
Repeated IDs are separate observations in this task. Keep their positions.
About this example
Original bounded source arrays; current browser calculations and complete spills are checked against independent source rules. Native client recalculation and edit/save are separate gates.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.