Level 8 · Boss: hand back a cleaned customer export

The cleaned copy

Angela’s request is the whole chapter in one sheet. The export holds the 47 Missouri customers, in A1:F48, exactly as the customer table has them: a city in capitals, a city with a leading space, a state with a trailing one, and two people who have two accounts each. You will add cleaned columns next to the raw ones and a problems column that explains every change, then count the rows that needed one.

The rule from the start of the chapter applies: the raw columns stay untouched. Marketing gets the clean columns; you keep the trail.

Step 1: city and state

G2   =PROPER(TRIM(E2))
H2   =UPPER(TRIM(F2))

Fill both down to row 48. Spot-check the rows the export got wrong: E15 (' Kansas City') and E19 (KANSAS CITY) both become Kansas City; E37 ('St. Louis ') becomes St. Louis; F4 (' MO') and F35 ('MO ') become MO.

Step 2: the email key and its count

I2   =LOWER(TRIM(D2))
J2   =COUNTIF($I$2:$I$48,I2)

Here the count is a number, not TRUE/FALSE: 2 tells marketing there are two accounts behind one person. Rows 47 and 48 are the later accounts of Edward Russell (row 10) and Noah Barnes (row 14). Both were exported with a leading space in the email, which is exactly the case a raw COUNTIF on column D misses.

Step 3: the problems column

Each check becomes one IF that returns a word or the empty text "":

Piece	Returns a word when
IF(EXACT(E2,G2),"","city")	the clean city differs from the raw one in any character, case included
IF(EXACT(F2,H2),"","state")	the same for the state
IF(J2>1,"duplicate","")	the email key appears more than once

TEXTJOIN(delimiter, ignore_empty, text1, text2, …) joins its pieces with the delimiter. With TRUE as the second argument it skips the empty ones, so you never get ", , duplicate":

K2   =TEXTJOIN(", ",TRUE,IF(EXACT(E2,G2),"","city"),IF(EXACT(F2,H2),"","state"),IF(J2>1,"duplicate",""))

Then count the rows that have any problem at all:

N1   =COUNTIF(K2:K48,"?*")

The criteria "?*" is a wildcard pattern: ? is exactly one character and * any number of them, so it matches any text with at least one character and leaves the empty results out. It returns 9. TEXTJOIN exists in Excel 2019 and later, Microsoft 365 and Google Sheets. In older Excel, join the pieces with & and wrap the result in TRIM.

Thinking like an analyst

Nine of 47 rows had something wrong, and the problems column tells marketing which and why. That makes the next question easy to answer: which copy of Edward Russell gets the invite? That is a business call (keep the account with the orders, here the original, rows 10 and 14), and the sheet makes it visible instead of making it silently.

Before you send any cleaned list, check three things:

• no clean value starts or ends with a space: =COUNTIF(G2:G48," *") and =COUNTIF(G2:G48,"* ") should both return 0;
• the duplicate rows come in pairs (here 4, an even number);
• the count in N1 matches what you expect from the raw data, so the formula didn’t flag everything or nothing.

Common mistakes

• Counting duplicates on the raw D column: in this export it finds no duplicate at all.
• Comparing with <> in step 3: KANSAS CITY would not be flagged, because <> ignores case.
• TEXTJOIN with FALSE as the second argument: the empty pieces stay and the result reads , , duplicate.
• An unlocked COUNTIF range in step 2: rows near the bottom stop finding their pairs.