Clean and join data
Want more background?
The idea in everyday life
Keep the original order list. Use a helper column to remove extra spaces, then match pickup times using each order’s number. You can check each result against the original rows before using the finished sheet.
A join connects tables using a shared value called a key, such as Customer ID. Missing, inconsistent, or duplicate keys can produce wrong matches.
Match each order to its pickup time
One list has orders. Another has pickup times. Match them by order number, and make missing information easy to spot.
Open a blank spreadsheet and name its sheet Orders. Copy the table below, click A1 once, and paste. Each header belongs in its own column. Prices are in USD; enter the numbers without typing a dollar sign. Start fresh for this lesson so the helper columns below are empty.
Six orders from a school fundraiser
Paste at A1.
| AOrder | BCategory | CStatus | DUnits | EUnit price |
|---|---|---|---|---|
| 101 | Books | Paid | 2 | 12 |
| 102 | Games | Paid | 1 | 30 |
| 103 | Books | Canceled | 1 | 12 |
| 104 | Books | Paid | 3 | 12 |
| 105 | Games | Paid | 2 | 30 |
| 106 | Books | Paid | 1 | 18 |
Copy as plain text
Pickup list
Paste at H1.
| HOrder | IPickup |
|---|---|
| 101 | Morning |
| 102 | Afternoon |
| 103 | Morning |
| 104 | Afternoon |
| 105 | Morning |
Copy as plain text
1. Remove extra spaces without changing the original
In C2, type Paid with one space before and after it. Type Clean status in G1. In G2, enter the formula below, then copy G2 into G3:G7.
=TRIM(C2)You should see: G2 shows Paid without surrounding spaces. C2 still holds the original text.
TRIM removes ordinary spaces at the edges and reduces repeated spaces between words. It does not fix a misspelled status; check the words too.
2. Find the pickup time for order 101
Paste the pickup list at H1. Its order numbers should appear once each. Type Pickup time in J1. In J2, enter:
=VLOOKUP(A2,$H$2:$I$6,2,FALSE)You should see: J2 shows Morning.
A2 is the order to find. H2:I6 is the lookup table; its first column holds order numbers. The 2 returns its second column, Pickup. FALSE requires an exact match. The $ marks keep the range fixed when copied.
3. Keep a missing match visible
Copy J2 into J3:J7. Order 106 has no pickup entry, so J7 shows #N/A. Replace the formula in J2 with the version below, then copy it down again.
=IFNA(VLOOKUP(A2,$H$2:$I$6,2,FALSE),"Check ID")You should see: J2:J7 shows Morning, Afternoon, Morning, Afternoon, Morning, Check ID.
IFNA replaces the missing-match error with a useful message. It does not make up a pickup time. The six order rows remain: five matches and one that needs checking.
4. Check for duplicate lookup numbers
Type Matches in K1. Enter the formula below in K2, then copy it to K3:K7.
=COUNTIF($H$2:$H$6,A2)You should see: K2:K7 shows 1, 1, 1, 1, 1, 0.
1 means one matching pickup record, 0 means none, and more than 1 means a duplicate to investigate. VLOOKUP returns the first match, so a plausible-looking time alone does not prove the source is correct.
More practice and troubleshooting
Check your expanded formulas
In J2 use =IFNA(VLOOKUP(A2,$H$2:$I$7,2,FALSE),"Check ID"). In K2 use =COUNTIF($H$2:$H$7,A2). Copy each through row 7.
Try a duplicate
Temporarily change H6 from 105 to 101. Order 101 now has two matches and order 105 has none. The lookup still shows a time for 101, but the Matches column catches the problem. Restore H6 to 105 before finishing.
What to finish
The two source lists, cleaned status and pickup helper columns, match counts, and a note explaining how the missing pickup was resolved.
Watch for this
Using names as unique identifiers when spelling, spacing, or duplicate people can change the match.
How can I tell I understand it?
You can report exactly how many records matched, did not match, or duplicated—and why.
Quick knowledge check
Answer all 3 questions. Get at least 2 right and the lesson completes automatically.
You’ll get an explanation for each answer. Signed in? Your results save to your account.
Pass the check. We’ll save the progress.
Answer all 3 questions above and get at least 2 right. You can review the explanations and try again.
Go to the knowledge checkOptional course overview video · same video as Lesson 1
Pivot Table Excel Tutorial
From Kevin Stratvert. Watch here or open it on YouTube .
A pivot summarizes what it receives; use the video's field list to notice how dirty categories or duplicated joined rows would silently distort the result.Tools, templates, and lesson sources
Tools and templates
Tools open in a new tab so you can keep your place here.
Sources and further reading
Explore the references behind this lesson.
Report a problem with this lesson
Tell us what went wrong. Please leave out personal information and private project contents.