John Serra
Back to Blog
Data AuditData ManagementData QualityreportingSmall Business AnalyticsSpreadsheet Managementspreadsheets

Map Inconsistent Customer Names to Stable IDs in Excel and Google Sheets

Build a reviewed alias crosswalk in Excel or Google Sheets that maps inconsistent customer spellings to stable customer IDs with an exact-match XLOOKUP and an explicit UNMAPPED NAME flag. Walk through an 8-invoice test across Google Sheets and Excel for the web, plus a reconciliation check block that catches raw-name splits, approximate-lookup misroutes, blank IFERROR omissions, and table resizes.

To standardize customer names in a spreadsheet, build a mapping table, sometimes called a crosswalk. Each row pairs one spelling from your export with one customer ID and one approved name. Then look up each invoice’s cleaned name with an exact-match XLOOKUP that returns “UNMAPPED NAME” when a spelling is missing. Skip approximate lookups and unreviewed fuzzy matches, and check the mapped totals against the export.

In the April example below, 8 invoices use 7 different spellings for 4 real customers. Grouped by the raw names, Summit Precision Tool looks like the top customer at $1,800.00. Mapped to IDs, Beacon Dental Clinic is the top customer, billed under three different names: $1,400.00 + $1,600.00 + $900.00 = $3,900.00.

My preferred tool is Google Sheets because a spreadsheet should be shareable. It should make collaborating with others easy. Every step below also covers Excel for the web. If your export already has a customer ID column, you’re one step further along, so find unmatched customer IDs with XLOOKUP instead. To keep new entries consistent from now on, use dropdowns to keep spreadsheet categories consistent. This article fixes the names that are already in your exports.

Unless a step says otherwise, the results below come from a test of this exact workbook in Google Sheets and Excel for the web, both in US English. Desktop Excel, Excel 2016 and 2019, and Power Query weren’t part of the test.

The Example Export

Below is the 8-row invoice export I made up for this article. It sits on a sheet named Customer_Map, with headings in A1:D1 and invoices in rows 2 to 9. The Row column is a row-number guide, not a worksheet column.

Row invoice_id (A) invoice_date (B) customer_name_raw (C) net_sales_usd (D)
2 INV-7101 2026-04-03 Beacon Dental Clinic $1,400.00
3 INV-7102 2026-04-06 Beacon Dental Lab $1,500.00
4 INV-7103 2026-04-09 Redwood Cold Storage $1,700.00
5 INV-7104 2026-04-12 Beacon Dental Clinic, LLC $1,600.00
6 INV-7105 2026-04-15 Summit Precision Tool $1,000.00
7 INV-7106 2026-04-19 Redwood Cold Storage Co. $1,100.00
8 INV-7107 2026-04-23 BDC – Downtown $900.00
9 INV-7108 2026-04-28 Summit Precision Tool $800.00

The export totals $10,000.00. It has no customer ID, only the name someone typed on each invoice. Rows 2, 5, and 8 are the same dental clinic. Row 3 is a different business, a dental lab, that happens to share the first two words. Rows 4 and 7 are the same cold-storage company.

Here are five ways of grouping this export report. The last two rows assume next month’s export spells row 8 as Beacon Dental Office, a spelling the crosswalk doesn’t have yet.

Method Invoices Total sales Customers Top customer
Crosswalk with exact XLOOKUP 8 $10,000.00 4 $3,900.00
Group on the raw names 8 $10,000.00 7 $1,800.00
Fuzzy or prefix match merges Clinic and Lab (hypothetical) 8 $10,000.00 3 $5,400.00
Approximate lookup on the new spelling 8 $10,000.00 4 $3,000.00
Exact lookup with a blank error fallback 7 $9,100.00 4 $3,000.00

Only the first row is right. Three of the wrong ones still total $10,000.00, so a total check alone won’t catch them.

Why Common Shortcuts Misassign Customers

Grouping on raw names. A pivot table or SUMIFS on column C treats every spelling as its own customer. Seven groups come back instead of four. Beacon Dental Clinic’s $3,900.00 splits into $1,400.00, $1,600.00, and $900.00, so Summit Precision Tool looks like the biggest customer at $1,800.00. Any repeat-customer rate or customer concentration check built on those groups is wrong, too.

Find and Replace on the export. Replacing “Beacon Dental Clinic, LLC” with “Beacon Dental Clinic” in column C fixes this month. It leaves no record of what changed, and next month’s export brings the old spelling back. A crosswalk keeps each decision in one row you can review.

To try the next two shortcuts yourself, build the workbook in Step 1 and Step 2 first. Then duplicate the Customer_Map tab and run the experiments on the copy, so the original stays as your unchanged benchmark. On the copy, change C8 to Beacon Dental Office, as if next month’s export spelled it that way, and type each test formula in an empty cell outside the tables, such as Y8.

Approximate lookup. VLOOKUP with its fourth argument left out or set to TRUE assumes a sorted list and returns the closest smaller match. On the A–Z crosswalk below, that’s Beacon Dental Lab, so VLOOKUP(F8, $K$2:$L$8, 2, TRUE) returns the lab’s ID, CUST-102. XLOOKUP(F8, $K$2:$K$8, $L$2:$L$8, "UNMAPPED NAME", -1). With match mode -1, it did the same. In a report that fills either lookup down all eight invoices, the $900.00 goes to the lab. Beacon Dental Lab rises from $1,500.00 to $2,400.00, Beacon Dental Clinic drops from $3,900.00 to $3,000.00, and the total still reads $10,000.00. Nothing on the sheet looks like an error.

Sorting doesn’t make approximate matching safe. With the crosswalk sorted Z–A, the approximate VLOOKUP returned the clinic’s ID for Beacon Dental Office. It also returned the clinic’s ID for Summit Precision Tool, a name that’s in the list exactly. Both programs gave the same results. On an unsorted list, an approximate VLOOKUP can misroute even a correct spelling. XLOOKUP with match mode -1 returned the lab’s ID whether the list was sorted A–Z or Z–A, because its default search runs from top to bottom and doesn’t need a sorted list.

A blank error fallback. On the same copy, an exact lookup with no fallback, such as XLOOKUP(F8, $K$2:$K$8, $L$2:$L$8) or VLOOKUP(F8, $K$2:$L$8, 2, FALSE), returns #N/A for Beacon Dental Office. That error is useful because you can see it. Wrapping the lookup as IFERROR(XLOOKUP(F8, $K$2:$K$8, $L$2:$L$8), "") hides it, and the cell goes blank. In a report that fills the wrapped lookup down all eight invoices, SUMIFS skips the blank row, and the report shows 7 invoices and $9,100.00 with no warning. The fix is a fallback that says something. The lookup in Step 2 returns “UNMAPPED NAME” instead of a blank.

Delete the copy when you’re done. Your original Customer_Map still has BDC – Downtown in C8.

Fuzzy matching. Power Query in Excel offers a fuzzy merge for matching similar text. Microsoft documents that it scores similarity with the Jaccard method, uses a default similarity threshold of 0.80, ignores case by default, and returns every match above the threshold unless you set a maximum number of matches. Set it to 1 to keep one match per row. Power Query wasn’t run for this article, so the numbers that follow are hypothetical. If a fuzzy match or a “starts with Beacon Dental” rule merged Beacon Dental Lab into Beacon Dental Clinic, you’d get 3 customers and a top customer of $5,400.00. Two separate businesses would become one.

A lookup table that lists the same alias twice causes a related problem. A merge can return two rows for one invoice. In the example, a duplicated Beacon Dental Clinic alias turns 8 invoices into 9 and $10,000.00 into $11,400.00, the same fan-out that explains why merging tables inflates your sales total.

A reviewed crosswalk avoids all five problems. A person approves each spelling once, the lookup accepts only exact spellings, and any new spelling stops the report until someone adds it.

Step 1: Build the Crosswalk

The workbook uses one sheet, Customer_Map, with four blocks side by side:

  • A1:I9 holds the invoices in columns A to D, plus helper formulas in E to I.
  • K1:N8 holds the crosswalk, with a check formula in column N.
  • P1:S6 holds the customer summary.
  • U1:W16 holds the reconciliation check.

Leave columns J, O, and T empty as spacers. Enter dates as real dates and amounts as plain numbers (1400, not “$1,400”). Then paste the formulas from the Formula Reference at the end before you try any of the experiments below.

The crosswalk has three typed columns and one check column:

Row alias_name (K) customer_id (L) canonical_name (M) Alias_Check (N)
2 BDC – Downtown CUST-101 Beacon Dental Clinic OK
3 Beacon Dental Clinic CUST-101 Beacon Dental Clinic OK
4 Beacon Dental Clinic, LLC CUST-101 Beacon Dental Clinic OK
5 Beacon Dental Lab CUST-102 Beacon Dental Lab OK
6 Redwood Cold Storage CUST-103 Redwood Cold Storage OK
7 Redwood Cold Storage Co. CUST-103 Redwood Cold Storage OK
8 Summit Precision Tool CUST-104 Summit Precision Tool OK

Three rules keep it trustworthy:

  • Each alias appears once. The check ignores case, so “beacon dental clinic” and “Beacon Dental Clinic” count as the same alias.
  • Each customer ID has one canonical name, and each canonical name belongs to one ID.
  • Each canonical name also appears as its own alias, as Beacon Dental Clinic does in row 3.

The formula in N2, copied down to N8, enforces them. It returns "OK" or one of four flags: "DUPLICATE ALIAS", "SPLIT CANONICAL" for an ID with two names, "MISSING SELF ALIAS", or "CHECK ALIAS" for a blank or badly formatted entry. Any flag stops the whole report.

The list is sorted A–Z here only so the approximate-lookup demonstration works. The exact lookup doesn’t need any order, so you can add new aliases at the bottom.

The helper formulas are strict on purpose, so the example flags a doubtful row instead of guessing. Column E marks an invoice row "CHECK ROW", and the report stops, unless all of these are true:

  • The invoice ID is uppercase text with no spaces, such as INV-7101, and appears only once. The duplicate check ignores case.
  • The date is a whole date from 2000-01-01 to 2100-01-01, with no time attached.
  • The amount is a number of zero or more, with no more than two decimal places.
  • Neither the invoice ID nor the customer name contains a line break or any of these characters: * ? ~ = < >

Column N applies similar rules to the crosswalk. Its text must already be trimmed, and every customer ID must be CUST- followed by three digits, such as CUST-101. The summary IDs in column P follow the same format. These are rules of this example workbook, not limits of XLOOKUP. A credit memo of -250, a date with a time, or an ID like C-104 fails the check. If your export has credits or a different ID format, redesign and retest the checks before you rely on them.

Raw names in column C don’t need to be tidy. Extra spaces and non-breaking spaces are fine there, because column F cleans them before the lookup. Column E checks the row but doesn’t change it.

If other people edit the workbook, protect the formula cells and the crosswalk in columns K to N, so only the person who approves aliases can change them.

Step 2: Look Up Each Invoice’s Customer ID

Column F cleans the raw name:

=IF(NOT(EXACT(E2, "OK")), "", TRIM(SUBSTITUTE(C2, CHAR(160), " ")))

SUBSTITUTE turns non-breaking spaces, which often come from web exports, into normal spaces. TRIM then removes spaces at the start and end and shrinks repeated spaces to one. Microsoft notes that TRIM alone doesn’t remove non-breaking spaces, which is why the formula needs both.

The lookup itself is one XLOOKUP with five arguments:

=XLOOKUP(F2, $K$2:$K$8, $L$2:$L$8, "UNMAPPED NAME", 0)

It looks for the cleaned name in the alias column and returns the customer ID from column L. The fourth argument is the text to show when the name isn’t in the list. The fifth, 0, asks for an exact match. That’s also the default, but writing it makes the intent clear to the next person who opens the sheet.

The full G2 in the Formula Reference wraps this lookup in checks, so it returns "CHECK INPUTS" if any invoice row or crosswalk row fails. H2 returns the canonical name from column M the same way. It also confirms that the customer ID exists in the summary in P2:P5, and returns "UNLISTED SUMMARY ID" if it doesn’t. I2 marks the first row of each distinct cleaned name with 1, so SUM(I2:I9) counts the 7 raw spellings. Enter E2:I2 and copy them down to row 9.

Row customer_name_raw (C) Mapped_Customer_ID (G) Mapped_Customer_Name (H)
2 Beacon Dental Clinic CUST-101 Beacon Dental Clinic
3 Beacon Dental Lab CUST-102 Beacon Dental Lab
4 Redwood Cold Storage CUST-103 Redwood Cold Storage
5 Beacon Dental Clinic, LLC CUST-101 Beacon Dental Clinic
6 Summit Precision Tool CUST-104 Summit Precision Tool
7 Redwood Cold Storage Co. CUST-103 Redwood Cold Storage
8 BDC – Downtown CUST-101 Beacon Dental Clinic
9 Summit Precision Tool CUST-104 Summit Precision Tool
Google Sheets view of Customer_Map!A1:I9 showing invoices INV-7101 through INV-7108 in A2:D9 totaling 10000.00, Row_Check all OK in column E, normalized customer names in column F, Mapped_Customer_ID CUST-101 through CUST-104 in column G, canonical Mapped_Customer_Name in column H, and First_Raw_Name_Flag totaling 7 distinct raw spellings in column I.
In Google Sheets, Customer_Map!A1:I9 pairs all eight raw invoices with stable IDs (CUST-101 through CUST-104) and flags 7 distinct raw spellings. Open full-size image.

The match ignores case. In the test, C2 was changed to “beacon dental clinic” in lowercase, with extra spaces in front, between the words, and at the end, including two non-breaking spaces. Column E still said OK, column F returned “beacon dental clinic”, and G2 still returned CUST-101 in both programs.

The summary in P1:S6 lists each customer once. Type the IDs and canonical names in P2:Q5. R2 counts the customer’s invoices with COUNTIF, and S2 adds their sales with SUMIFS on the mapped ID in column G. Copy both down to row 5. Row 6 holds the totals, labeled Mapped_Summary_Total in P6 and ALL_MAPPED in Q6.

Row customer_id (P) canonical_name (Q) invoice_count (R) net_sales_usd (S)
2 CUST-101 Beacon Dental Clinic 3 $3,900.00
3 CUST-102 Beacon Dental Lab 1 $1,500.00
4 CUST-103 Redwood Cold Storage 2 $2,800.00
5 CUST-104 Summit Precision Tool 2 $1,800.00
6 Mapped_Summary_Total ALL_MAPPED 8 $10,000.00
Google Sheets view of Customer_Map!K1:S8 displaying the 7-row alias crosswalk in K2:N8 with all Alias_Check rows OK and the canonical customer summary in P1:S6 totaling 8 invoices and 10000.00 across CUST-101 Beacon Dental Clinic 3900.00, CUST-102 Beacon Dental Lab 1500.00, CUST-103 Redwood Cold Storage 2800.00, and CUST-104 Summit Precision Tool 1800.00.
Beside the invoice grid, K1:N8 stores the seven approved aliases while P1:S6 reconciles the four canonical customer totals to 8 invoices and $10,000.00. Open full-size image.

Beacon Dental Clinic is now the top customer at $3,900.00, and Summit Precision Tool drops to third.

XLOOKUP isn’t available in Excel 2016 or Excel 2019. For those versions, the Formula Reference includes replacement G2 and H2 formulas that use INDEX, MATCH with a match type of 0, and IFNA. Replace both, so no XLOOKUP is left on the sheet. In the test, the replacements gave the same results as the XLOOKUP versions in Google Sheets and Excel for the web, including the stops for a missing alias and a missing summary ID. Excel 2016 and 2019 themselves weren’t tested.

To see what a new spelling does, duplicate the Customer_Map tab again and work on the copy. Change C8 on the copy to Beacon Dental Office, as if next month’s export used a new spelling. G8 and H8 show “UNMAPPED NAME”. The summary total in S6 drops to $9,100.00, and the status in V16 says STOP: CROSSWALK OMITS $900.00 (1 UNMAPPED CUSTOMER ROW). That’s the result you want: the report stops until a person confirms who Beacon Dental Office is.

Google Sheets audit panel U1:W16 when invoice C8 changes to unmapped Beacon Dental Office, showing Summary_Grid_Sales_USD at 9100.00, Mapped_vs_Source_Sales_Gap_USD at -900.00, Unmapped_Or_Unlisted_Rows at 1, and Audit_Status halting both V16 and W16 with STOP: CROSSWALK OMITS $900.00 (1 UNMAPPED CUSTOMER ROW).
Changing C8 to unmapped Beacon Dental Office drops the mapped summary to $9,100.00 and halts V16/W16 at STOP: CROSSWALK OMITS $900.00 (1 UNMAPPED CUSTOMER ROW). Open full-size image.

If it’s the clinic, the fix is one new crosswalk row. On the copy, add Beacon Dental Office, CUST-101, Beacon Dental Clinic in K9:M9. Until you resize the crosswalk ranges, V16 says STOP: TRAILING CROSSWALK ROWS OUTSIDE LOOKUP RANGE (RESIZE CROSSWALK), because row 9 sits below the lookup range. After the resize described in the section on export size below, G8 should return CUST-101 and V16 should reconcile at $10,000.00 again. The test didn’t run this exact repair, but it grew the crosswalk the same way with other new aliases, and both programs reconciled.

Delete the copy before Step 3. The original Customer_Map should still have BDC – Downtown in C8, seven aliases ending in row 8, four customers in the summary, and NOT TESTED in W2.

Step 3: Reconcile the Mapped Totals

The check block in U1:W16 answers two questions. Column V asks whether the crosswalk and summary account for every invoice in the export. Column W lets you test another report against that benchmark, such as a pivot table or a dashboard someone else built.

Row Metric (U) Column V at baseline What goes in column W
2 Audit_Scope EXACT_CROSSWALK (typed) Type NOT TESTED, or EXACT_CROSSWALK when you enter readings
3 Invalid_Row_Or_Rule_Count 0 Type 0
4 Trailing_Cells_Below_Tables 0 Type 0
5 Source_Invoice_Rows 8 Invoice count from the other report
6 Source_Total_Sales_USD 10000.00 Total sales from the other report
7 Active_Mapped_Customers 4 Number of customers with at least one invoice in the other report
8 Top_Customer_Sales_USD 3900.00 Largest customer total in the other report
9 Min_Summary_Customer_USD 1500.00 Smallest customer total in the other report
10 Raw_Distinct_Name_Variants 7 Formula: other report’s customers minus the summary’s
11 Summary_Grid_Sales_USD 10000.00 Formula: other report’s sales minus the summary’s
12 Mapped_vs_Source_Sales_Gap_USD 0.00 Formula: other report’s sales minus the source’s
13 Direct_vs_Summary_CrossCheck_USD 0.00 Formula: shift in the top and smallest customer totals
14 Summary_Invoice_Count_Gap 0 Formula: other report’s invoices minus the source’s
15 Unmapped_Or_Unlisted_Rows 0 Formula: input checks, then the invoice count gap
16 Audit_Status Status message Status message

V3 counts invalid or blank cells across all three tables. V4 is the guard for rows added below a table. It counts every filled cell in columns A to I, K to N, and P to S, and subtracts the cells inside each table. Anything below the tables, whether in row 10 or row 500, makes it greater than 0 and stops the report. V15 counts invoices marked “UNMAPPED NAME” or “UNLISTED SUMMARY ID”.

V7 counts only customers with at least one invoice, so enter the same count in W7 and leave out customers with no invoices this month. V9 works the other way. It’s the smallest total across every summary row, including a customer at $0.00. Read W9 the same way, and include customers that show $0.00 in the other report.

Until you have readings, leave W2 and W5:W9 at NOT TESTED and W3:W4 at 0. To check a report, type EXACT_CROSSWALK in W2 and the five readings in W5:W9. Set them back to NOT TESTED after each check, so one test doesn’t carry into the next.

Here’s what W16 said for each shortcut from earlier:

Report being checked Readings in W5:W9 W16 says
Matches the crosswalk 8, 10000, 4, 3900, 1500 RECONCILED: CANDIDATE TOTALS AND BOUNDS MATCH CROSSWALK ($10000.00 ACROSS 4 CUSTOMERS)
Grouped on raw names 8, 10000, 7, 1800, 900 STOP: CUSTOMER COUNT MISMATCH (7 VS 4; UNMAPPED NAME VARIANTS SPLIT ACCOUNTS)
Clinic and Lab merged (hypothetical) 8, 10000, 3, 5400, 1800 STOP: FALSE ENTITY MERGE (3 VS 4 CUSTOMERS; DISTINCT ACCOUNTS COLLAPSED)
Approximate lookup on Beacon Dental Office 8, 10000, 4, 3000, 1800 STOP: CUSTOMER TOTALS MISROUTED ($1200.00 TOP/MIN SHIFT; CHECK APPROXIMATE OR WRONG ALIAS)
Blank error fallback 7, 9100, 4, 3000, 1500 STOP: CANDIDATE INVOICE COUNT MISMATCH (7 VS 8; $900.00 SALES GAP)
Duplicated alias 9, 11400, 4, 5300, 1500 STOP: CANDIDATE INVOICE COUNT MISMATCH (9 VS 8; $1400.00 SALES GAP)
Google Sheets view of Customer_Map!U1:W16 showing both Exact_Crosswalk_Benchmark in column V and Candidate_Method_Audit in column W passing all checks at 8 invoices, 10000.00 sales, 4 customers, top 3900.00, min 1500.00, zero gaps in rows 12 to 15, and RECONCILED Audit_Status messages in V16 and W16.
When the candidate report matches the verified crosswalk benchmark in U1:W16, every gap row reads zero and both V16 and W16 show RECONCILED. Open full-size image.

In the test, the raw-name, approximate-lookup, and blank-fallback readings came from small probe reports built on separate sheets in both programs. The merge and duplicated-alias readings were typed in to match those mistakes, because Power Query wasn’t run.

The approximate lookup is the case a total check misses. The invoice count, total, and customer count all match. Only the bounds move: the top customer falls by $900.00 and the smallest rises by $300.00, so W13 shows 1200.00 and the report stops.

Excel for the Web audit block U1:W16 comparing the RECONCILED benchmark in column V (Top 3900.00, Min 1500.00) against the approximate-lookup candidate readings in column W (8 invoices, 10000.00 sales, 4 customers, Top 3000.00, Min 1800.00), displaying Direct_vs_Summary_CrossCheck_USD at 1200.00 in W13 and halting W16 with STOP: CUSTOMER TOTALS MISROUTED ($1200.00 TOP/MIN SHIFT; CHECK APPROXIMATE OR WRONG ALIAS).
In Excel for the web, an approximate lookup still shows 8 invoices, 4 customers, and $10,000.00, but W13 catches the $1,200.00 top/min shift and halts W16. Open full-size image.

Column V also stops on its own when the workbook itself changes. These are the live edits from the test. To repeat one, start from a fresh duplicate of the untouched Customer_Map tab, make that single change on the copy, and delete the copy afterward:

Change on Customer_Map V16 says
C8 = Beacon Dental Office STOP: CROSSWALK OMITS $900.00 (1 UNMAPPED CUSTOMER ROW)
K4 = beacon dental clinic STOP: DUPLICATE ALIAS IN CROSSWALK (EACH ALIAS MUST BE UNIQUE)
New alias in row 9, ranges not resized STOP: TRAILING CROSSWALK ROWS OUTSIDE LOOKUP RANGE (RESIZE CROSSWALK)

Changing W2:W9 back to NOT TESTED and 0 doesn’t undo an edit like these, so use a new copy for each one. When column V stops, column W shows the same message, so fix the workbook before you check any other report.

When Next Month’s Export Changes Size

Each table can grow or shrink on its own. Every range in the formulas ends at a fixed last row, so a resize means replacing the old last row with the new one inside the formulas.

Find the last rows before you change anything:

  • Invoices: the invoice count plus 1. Eight invoices end at row 9.
  • Crosswalk: the alias count plus 1. Seven aliases end at row 8.
  • Summary: the customer count plus 1 for the last customer, and plus 2 for the totals row. Four customers end at row 5, with totals in row 6.

Then choose the Find and Replace option. Select from E2 across to column W, down to the largest of these: row 16, the invoice last row, the crosswalk last row, and the summary totals row. In the example, that’s E2:W16. With 23 aliases and 20 customers, the crosswalk ends at row 24, so you’d select E2:W24.

The selection has to reach every summary row because the summary formulas also read the invoice ranges. In the test, a workbook with 8 invoices, 23 aliases, and 20 customers added a ninth invoice for $1,100.00 using only E2:W16. One customer’s summary formulas kept the old invoice range, and V16 said STOP: SUMMARY DIFFERS FROM SOURCE BY $1100.00. With E2:W24, it reconciled at $11,100.00 across 9 invoices.

To resize the invoices:

  1. When the export grows, type the new invoices in columns A to D below the old last row, and leave E to I empty in those rows for now. When it shrinks, don’t clear anything yet.
  2. In your selection, replace :$A$ followed by the old last row with :$A$ followed by the new last row. Do the same for columns B to I, so nine replacements in all. Going from 8 invoices to 9, that’s :$A$9 to :$A$10, through :$I$9 to :$I$10.
  3. When the export grows, copy E2:I2 down to the new last row. When it shrinks, clear the unused cells in A to I only, from the row below the new last row down to the old last row.

To resize the crosswalk, follow the same steps on columns K to M. Replace the old last row in :$K$, :$L$, :$M$, and :$N$, so four replacements. Then copy N2 down to the new last row, or clear the unused cells in K to N. Adding Beacon Dental Office as an eighth alias means replacing :$K$8 with :$K$9, and the same for L, M, and N.

To resize the summary, move the totals row with Insert cells, so the other tables stay where they are.

  • Growing from 4 customers to 5: Select P6:S6 and insert cells, shifting down. In Google Sheets, that’s Insert > Cells > Insert cells and shift down. In Excel for the web, it’s Home > Insert > Insert Cells & Shift Down. The totals move to row 7, and both programs updated the formulas automatically, which point at the totals row on their own. Type the new ID and name in P6:Q6. Then select from E2 across to column W, down to the largest of row 16, the invoice last row, the crosswalk last row, and the new totals row. In the example, that’s still E2:W16. Replace :$P$5 with :$P$6, and the same for Q, R, and S. Then copy R2:S2 down to row 6.
  • Shrinking: Make the four replacements first, using the same full selection, with the current totals row as the last term. Then select the unused summary rows in P to S and delete cells, shifting up. In Google Sheets, that’s Edit > Delete > Cells and shift up. In Excel for the web, it’s Home > Delete > Delete Cells & Shift Up.

For the replacements, keep Find and Replace inside your selection and make it search the formulas:

  • Google Sheets: Choose Edit > Find and replace, set Search to Specific range, and check Also search within formulas. Leave Match entire cell contents unchecked. In the test, the formulas checkbox was unchecked every time the dialog opened, so check it each time. Leave Search using regular expressions unchecked too, because the $ sign has a special meaning there.
  • Excel for the web: Choose Home > Find & Select > Replace, set Within to Selection under the search options, and leave Match entire cell contents unchecked. The tested dialog had no option for searching formulas, and Replace All changed the formula references in the selection directly.

Replace only those strings, and always start from the current last row. Never insert or delete whole worksheet rows, because that moves or cuts the tables beside the one you’re changing. The test shrank the invoices from 8 to 4 and grew them to 9, while the crosswalk went from 7 to 4 to 8. It also ran an export of 105 invoices and added a fifth customer, Cedar Ridge Logistics, across all three tables. Every chain reconciled in both programs.

What These Checks Can’t Prove

The check in column W compares five numbers. It doesn’t compare each customer. If another report moves $100.00 from Redwood Cold Storage to Summit Precision Tool, showing $2,700.00 and $1,900.00, the count, total, customer count, top, and smallest totals all stay the same. In the test, W16 still said reconciled. Compare each customer’s ID, invoice count, and total in the other report against P2:S5 before you trust the split by customer.

A crosswalk can also be consistent and still wrong. If BDC – Downtown were mapped to CUST-102 by mistake, every check would pass, and V16 would reconcile at $10,000.00. Only a person can confirm who an alias belongs to. For any doubtful spelling, check the billing address, tax ID, or contract before you add it.

The check also can’t tell you whether an invoice was missing from the export. Compare the invoice count in V5 and the total in V6 with your billing or point-of-sale system for the same dates.

Frequently Asked Questions

Should customer name mapping be case-sensitive?

Usually not. An exact-match XLOOKUP ignores case, so “beacon dental clinic” finds Beacon Dental Clinic. The duplicate check in column N ignores case too, so the crosswalk can’t hold two capitalizations of one name with different IDs. Customer IDs are different. The checks compare them with EXACT, so CUST-101 has to be uppercase.

Can I use INDEX and MATCH in Excel 2016 or 2019?

Yes. Replace both G2 and H2 with the replacement formulas in the Formula Reference, and copy them down to row 9. MATCH with a match type of 0 is an exact match, but it treats * and ? as wildcards, and ~ is the character that cancels them. This workbook blocks all three characters in columns E and N, so keep those checks in place. The replacements were tested in Google Sheets and Excel for the web, not in Excel 2016 or 2019.

What if two different companies have the same billing name?

A name-only crosswalk can’t separate them, because each alias maps to one ID. You’d need a combined key built from the name and something that tells the companies apart, such as the billing ZIP code or an account number, in both the invoices and the crosswalk. Use a separator the checks allow, such as |. That’s a separate workbook design, not a small edit to this one. The cleaning formula, the lookup, and every duplicate and self-alias check need to be rebuilt and retested. Keep ZIP codes as text, so you don’t lose leading zeros when you import the CSV.

What to Do Next

Once every invoice carries a stable ID, use column G instead of the raw names for any customer metric, such as repeat-customer rate or revenue concentration. When a new spelling shows up, confirm who it is, add one crosswalk row, resize the crosswalk, and check that V16 reconciles before you share the report. If you clean the export in other ways first, make the crosswalk one step in a repeatable workflow for cleaning business spreadsheet data.

Formula Reference

Each label before = names the destination cell; paste only the formula. Fill E2:I2 down to row 9 and N2 down to N8. Fill R2:S2 down to row 5. Paste R6, S6, and the column V and W formulas one cell at a time.

Headings: A1:I1 are invoice_id, invoice_date, customer_name_raw, net_sales_usd, Row_Check, Normalized_Name, Mapped_Customer_ID, Mapped_Customer_Name, and First_Raw_Name_Flag. K1:N1 are alias_name, customer_id, canonical_name, and Alias_Check. P1:S1 are customer_id, canonical_name, invoice_count, and net_sales_usd. U1:W1 are Metric, Exact_Crosswalk_Benchmark, and Candidate_Method_Audit, with the metric labels from the check-block table in U2:U16.

Typed inputs: P6 = Mapped_Summary_Total, Q6 = ALL_MAPPED, V2 = EXACT_CROSSWALK, W2 = NOT TESTED, W3 and W4 = 0, and W5:W9 = NOT TESTED.

Invoice helpers:

E2: =IF(OR(COUNTA(A2:D2)<4, NOT(ISTEXT(A2)), NOT(ISNUMBER(B2)), NOT(ISTEXT(C2)), NOT(ISNUMBER(D2)), TRIM(SUBSTITUTE(A2, CHAR(160), " "))="", TRIM(SUBSTITUTE(C2, CHAR(160), " "))="", NOT(EXACT(A2, TRIM(SUBSTITUTE(A2, CHAR(160), " ")))), NOT(EXACT(A2, UPPER(A2))), NOT(ISERROR(FIND(" ", A2))), NOT(ISERROR(FIND(CHAR(10), A2&C2))), NOT(ISERROR(FIND(CHAR(13), A2&C2))), NOT(ISERROR(FIND("*", A2&C2))), NOT(ISERROR(FIND("?", A2&C2))), NOT(ISERROR(FIND("~", A2&C2))), NOT(ISERROR(FIND("=", A2&C2))), NOT(ISERROR(FIND("<", A2&C2))), NOT(ISERROR(FIND(">", A2&C2)))), "CHECK ROW", IF(OR(INT(B2)<>B2, B2<DATE(2000,1,1), B2>DATE(2100,1,1), D2<0, ROUND(D2, 2)<>D2, SUMPRODUCT(--(UPPER($A$2:$A$9)=UPPER(A2)))>1), "CHECK ROW", "OK"))
F2: =IF(NOT(EXACT(E2, "OK")), "", TRIM(SUBSTITUTE(C2, CHAR(160), " ")))
G2: =IF(OR(NOT(EXACT(E2, "OK")), COUNTIF($E$2:$E$9, "OK")<ROWS($A$2:$A$9), COUNTIF($N$2:$N$8, "OK")<ROWS($K$2:$K$8)), "CHECK INPUTS", XLOOKUP(F2, $K$2:$K$8, $L$2:$L$8, "UNMAPPED NAME", 0))
H2: =IF(EXACT(G2, "CHECK INPUTS"), "CHECK INPUTS", IF(EXACT(G2, "UNMAPPED NAME"), "UNMAPPED NAME", IF(SUMPRODUCT(--EXACT($P$2:$P$5, G2))=0, "UNLISTED SUMMARY ID", XLOOKUP(F2, $K$2:$K$8, $M$2:$M$8, "UNMAPPED NAME", 0))))
I2: =IF(OR(NOT(EXACT(E2, "OK")), COUNTIF($E$2:$E$9, "OK")<ROWS($A$2:$A$9)), "", IF(SUMPRODUCT(--(UPPER($F$2:F2)=UPPER(F2)))=1, 1, 0))

Excel 2016 and 2019 replacements for G2 and H2 (replace both):

G2: =IF(OR(NOT(EXACT(E2, "OK")), COUNTIF($E$2:$E$9, "OK")<ROWS($A$2:$A$9), COUNTIF($N$2:$N$8, "OK")<ROWS($K$2:$K$8)), "CHECK INPUTS", IFNA(INDEX($L$2:$L$8, MATCH(F2, $K$2:$K$8, 0)), "UNMAPPED NAME"))
H2: =IF(EXACT(G2, "CHECK INPUTS"), "CHECK INPUTS", IF(EXACT(G2, "UNMAPPED NAME"), "UNMAPPED NAME", IF(SUMPRODUCT(--EXACT($P$2:$P$5, G2))=0, "UNLISTED SUMMARY ID", IFNA(INDEX($M$2:$M$8, MATCH(F2, $K$2:$K$8, 0)), "UNMAPPED NAME"))))

Crosswalk check:

N2: =IF(OR(COUNTA(K2:M2)<3, NOT(ISTEXT(K2)), NOT(ISTEXT(L2)), NOT(ISTEXT(M2)), TRIM(SUBSTITUTE(K2, CHAR(160), " "))="", TRIM(SUBSTITUTE(L2, CHAR(160), " "))="", TRIM(SUBSTITUTE(M2, CHAR(160), " "))="", NOT(EXACT(K2, TRIM(SUBSTITUTE(K2, CHAR(160), " ")))), NOT(EXACT(L2, TRIM(SUBSTITUTE(L2, CHAR(160), " ")))), NOT(EXACT(M2, TRIM(SUBSTITUTE(M2, CHAR(160), " ")))), LEN(L2)<>8, NOT(EXACT(LEFT(L2, 5), "CUST-")), NOT(ISNUMBER(FIND(MID(L2, 6, 1), "0123456789"))), NOT(ISNUMBER(FIND(MID(L2, 7, 1), "0123456789"))), NOT(ISNUMBER(FIND(MID(L2, 8, 1), "0123456789"))), NOT(ISERROR(FIND(CHAR(10), K2&L2&M2))), NOT(ISERROR(FIND(CHAR(13), K2&L2&M2))), NOT(ISERROR(FIND("*", K2&L2&M2))), NOT(ISERROR(FIND("?", K2&L2&M2))), NOT(ISERROR(FIND("~", K2&L2&M2))), NOT(ISERROR(FIND("=", K2&L2&M2))), NOT(ISERROR(FIND("<", K2&L2&M2))), NOT(ISERROR(FIND(">", K2&L2&M2)))), "CHECK ALIAS", IF(SUMPRODUCT(--(UPPER($K$2:$K$8)=UPPER(K2)))>1, "DUPLICATE ALIAS", IF(OR(SUMPRODUCT(--EXACT($L$2:$L$8, L2), --NOT(EXACT($M$2:$M$8, M2)))>0, SUMPRODUCT(--(UPPER($M$2:$M$8)=UPPER(M2)), --NOT(EXACT($L$2:$L$8, L2)))>0), "SPLIT CANONICAL", IF(SUMPRODUCT(--(UPPER($K$2:$K$8)=UPPER(M2)), --EXACT($L$2:$L$8, L2))=0, "MISSING SELF ALIAS", "OK"))))

Customer summary:

R2: =IF(OR(COUNTIF($E$2:$E$9, "OK")<ROWS($A$2:$A$9), COUNTIF($N$2:$N$8, "OK")<ROWS($K$2:$K$8), COUNTIF($G$2:$G$9, "CHECK INPUTS")>0, NOT(ISTEXT($P2)), NOT(ISTEXT($Q2)), TRIM(SUBSTITUTE($P2, CHAR(160), " "))="", TRIM(SUBSTITUTE($Q2, CHAR(160), " "))="", NOT(EXACT($P2, TRIM(SUBSTITUTE($P2, CHAR(160), " ")))), NOT(EXACT($Q2, TRIM(SUBSTITUTE($Q2, CHAR(160), " ")))), LEN($P2)<>8, NOT(EXACT(LEFT($P2, 5), "CUST-")), NOT(ISNUMBER(FIND(MID($P2, 6, 1), "0123456789"))), NOT(ISNUMBER(FIND(MID($P2, 7, 1), "0123456789"))), NOT(ISNUMBER(FIND(MID($P2, 8, 1), "0123456789"))), NOT(ISERROR(FIND(CHAR(10), $P2&$Q2))), NOT(ISERROR(FIND(CHAR(13), $P2&$Q2))), NOT(ISERROR(FIND("*", $P2&$Q2))), NOT(ISERROR(FIND("?", $P2&$Q2))), NOT(ISERROR(FIND("~", $P2&$Q2))), NOT(ISERROR(FIND("=", $P2&$Q2))), NOT(ISERROR(FIND("<", $P2&$Q2))), NOT(ISERROR(FIND(">", $P2&$Q2))), SUMPRODUCT(--(UPPER($P$2:$P$5)=UPPER($P2)))>1, SUMPRODUCT(--(UPPER($Q$2:$Q$5)=UPPER($Q2)))>1, SUMPRODUCT(--EXACT($L$2:$L$8, $P2), --EXACT($M$2:$M$8, $Q2))=0), "CHECK INPUTS", COUNTIF($G$2:$G$9, $P2))
S2: =IF(NOT(ISNUMBER(R2)), "CHECK INPUTS", ROUND(SUMIFS($D$2:$D$9, $G$2:$G$9, $P2), 2))
R6: =IF(COUNT($R$2:$R$5)<ROWS($P$2:$P$5), "CHECK INPUTS", SUM($R$2:$R$5))
S6: =IF(OR(NOT(ISNUMBER($R$6)), COUNT($S$2:$S$5)<ROWS($P$2:$P$5)), "CHECK INPUTS", ROUND(SUM($S$2:$S$5), 2))

Benchmark checks:

V3: =COUNTIF($E$2:$E$9, "<>OK")+COUNTBLANK($A$2:$A$9)+COUNTBLANK($B$2:$B$9)+COUNTBLANK($C$2:$C$9)+COUNTBLANK($D$2:$D$9)+COUNTIF($N$2:$N$8, "<>OK")+COUNTBLANK($K$2:$K$8)+COUNTBLANK($L$2:$L$8)+COUNTBLANK($M$2:$M$8)+COUNTBLANK($P$2:$P$5)+COUNTBLANK($Q$2:$Q$5)
V4: =(COUNTA($A:$I)-COUNTA($A$1:$I$9))+(COUNTA($K:$N)-COUNTA($K$1:$N$8))+(COUNTA($P:$S)-COUNTA($P$1:$S$6))
V5: =IF(OR(NOT(EXACT(V2, "EXACT_CROSSWALK")), V3>0, V4>0, COUNT($I$2:$I$9)<ROWS($A$2:$A$9), NOT(ISNUMBER($R$6)), NOT(ISNUMBER($S$6))), "CHECK INPUTS", COUNTA($A$2:$A$9))
V6: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", ROUND(SUM($D$2:$D$9), 2))
V7: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", COUNTIF($R$2:$R$5, ">0"))
V8: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", ROUND(MAX($S$2:$S$5), 2))
V9: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", ROUND(MIN($S$2:$S$5), 2))
V10: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", SUM($I$2:$I$9))
V11: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", ROUND($S$6, 2))
V12: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", ROUND(V11-V6, 2))
V13: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", ROUND(ABS($S$6-SUMIFS($D$2:$D$9, $H$2:$H$9, "<>UNMAPPED NAME", $H$2:$H$9, "<>UNLISTED SUMMARY ID")), 2))
V14: =IF(NOT(ISNUMBER(V5)), "CHECK INPUTS", V5-$R$6)
V15: =IF(OR(NOT(ISNUMBER(V5)), COUNTIF($H$2:$H$9, "CHECK INPUTS")>0), "CHECK INPUTS", COUNTIF($H$2:$H$9, "UNMAPPED NAME")+COUNTIF($H$2:$H$9, "UNLISTED SUMMARY ID"))
V16: =IF(COUNTIF($E$2:$E$9, "<>OK")>0, "STOP: INVALID OR BLANK INVOICE ROWS", IF(COUNTIF($N$2:$N$8, "DUPLICATE ALIAS")>0, "STOP: DUPLICATE ALIAS IN CROSSWALK (EACH ALIAS MUST BE UNIQUE)", IF(COUNTIF($N$2:$N$8, "SPLIT CANONICAL")>0, "STOP: INCONSISTENT CANONICAL ID OR NAME IN CROSSWALK", IF(COUNTIF($N$2:$N$8, "MISSING SELF ALIAS")>0, "STOP: CANONICAL NAME MISSING SELF-ALIAS ROW IN CROSSWALK", IF(V3>0, "STOP: INVALID CROSSWALK OR SUMMARY LABELS", IF(COUNTA($K:$N)-COUNTA($K$1:$N$8)>0, "STOP: TRAILING CROSSWALK ROWS OUTSIDE LOOKUP RANGE (RESIZE CROSSWALK)", IF(COUNTA($A:$I)-COUNTA($A$1:$I$9)>0, "STOP: TRAILING INVOICE DATA OUTSIDE TABLE RANGE (RESIZE INVOICES)", IF(V4>0, "STOP: TRAILING CUSTOMER SUMMARY ROWS (RESIZE SUMMARY)", IF(NOT(ISNUMBER(V5)), "STOP: CHECK AUDIT SCOPE OR SUMMARY HEADERS", IF(AND(COUNTIF($H$2:$H$9, "UNMAPPED NAME")>0, COUNTIF($H$2:$H$9, "UNLISTED SUMMARY ID")>0), "STOP: CROSSWALK OMITS $" & TEXT(ABS(V12), "0.00") & " (" & V15 & " UNMAPPED AND UNLISTED SUMMARY ROWS)", IF(COUNTIF($H$2:$H$9, "UNMAPPED NAME")>0, "STOP: CROSSWALK OMITS $" & TEXT(ABS(V12), "0.00") & " (" & V15 & IF(V15=1, " UNMAPPED CUSTOMER ROW)", " UNMAPPED CUSTOMER ROWS)"), IF(COUNTIF($H$2:$H$9, "UNLISTED SUMMARY ID")>0, "STOP: SUMMARY OMITS $" & TEXT(ABS(V12), "0.00") & " (" & V15 & IF(V15=1, " UNLISTED CUSTOMER ID ROW)", " UNLISTED CUSTOMER ID ROWS)"), IF(OR(V12<>0, V13<>0, V14<>0, V15<>0), "STOP: SUMMARY DIFFERS FROM SOURCE BY $" & TEXT(ABS(V12), "0.00"), "RECONCILED: MAPPED TOTALS MATCH SOURCE ($" & TEXT(V6, "0.00") & " ACROSS " & V5 & " INVOICES; " & V10 & " RAW NAMES UNIFIED TO " & V7 & " CUSTOMERS)")))))))))))))

Checks for another report:

W10: =IF(EXACT(W15, "NOT TESTED"), "NOT TESTED", IF(OR(NOT(ISNUMBER(W15)), NOT(ISNUMBER(V7)), NOT(ISNUMBER(W7))), "CHECK INPUTS", W7-V7))
W11: =IF(EXACT(W15, "NOT TESTED"), "NOT TESTED", IF(OR(NOT(ISNUMBER(W15)), NOT(ISNUMBER(V11)), NOT(ISNUMBER(W6))), "CHECK INPUTS", ROUND(W6-V11, 2)))
W12: =IF(EXACT(W15, "NOT TESTED"), "NOT TESTED", IF(OR(NOT(ISNUMBER(W15)), NOT(ISNUMBER(V6)), NOT(ISNUMBER(W6))), "CHECK INPUTS", ROUND(W6-V6, 2)))
W13: =IF(EXACT(W15, "NOT TESTED"), "NOT TESTED", IF(OR(NOT(ISNUMBER(W15)), NOT(ISNUMBER(V8)), NOT(ISNUMBER(V9)), NOT(ISNUMBER(W8)), NOT(ISNUMBER(W9))), "CHECK INPUTS", ROUND(ABS(W8-V8)+ABS(W9-V9), 2)))
W14: =IF(EXACT(W15, "NOT TESTED"), "NOT TESTED", IF(OR(NOT(ISNUMBER(W15)), NOT(ISNUMBER(V5)), NOT(ISNUMBER(W5))), "CHECK INPUTS", W5-V5))
W15: =IF(OR(V3>0, V4>0, NOT(ISNUMBER(V5)), NOT(ISNUMBER(V12)), NOT(ISNUMBER(V13)), NOT(ISNUMBER(V14)), NOT(ISNUMBER(V15)), NOT(ISTEXT(W2)), NOT(ISNUMBER(W3)), NOT(ISNUMBER(W4))), "CHECK INPUTS", IF(OR(V12<>0, V13<>0, V14<>0, V15<>0, INT(W3)<>W3, W3<>0, INT(W4)<>W4, W4<>0), "CHECK INPUTS", IF(AND(EXACT(W2, "NOT TESTED"), EXACT(W5, "NOT TESTED"), EXACT(W6, "NOT TESTED"), EXACT(W7, "NOT TESTED"), EXACT(W8, "NOT TESTED"), EXACT(W9, "NOT TESTED")), "NOT TESTED", IF(OR(NOT(EXACT(W2, "EXACT_CROSSWALK")), NOT(ISNUMBER(W5)), NOT(ISNUMBER(W6)), NOT(ISNUMBER(W7)), NOT(ISNUMBER(W8)), NOT(ISNUMBER(W9))), "CHECK INPUTS", IF(OR(INT(W5)<>W5, W5<0, W6<0, ROUND(W6, 2)<>W6, INT(W7)<>W7, W7<0, W8<0, ROUND(W8, 2)<>W8, W9<0, ROUND(W9, 2)<>W9, W8<W9, W8>W6), "CHECK INPUTS", W5-$R$6)))))
W16: =IF(COUNTIF($E$2:$E$9, "<>OK")>0, "STOP: INVALID OR BLANK INVOICE ROWS", IF(COUNTIF($N$2:$N$8, "DUPLICATE ALIAS")>0, "STOP: DUPLICATE ALIAS IN CROSSWALK (EACH ALIAS MUST BE UNIQUE)", IF(COUNTIF($N$2:$N$8, "SPLIT CANONICAL")>0, "STOP: INCONSISTENT CANONICAL ID OR NAME IN CROSSWALK", IF(COUNTIF($N$2:$N$8, "MISSING SELF ALIAS")>0, "STOP: CANONICAL NAME MISSING SELF-ALIAS ROW IN CROSSWALK", IF(V3>0, "STOP: INVALID CROSSWALK OR SUMMARY LABELS", IF(COUNTA($K:$N)-COUNTA($K$1:$N$8)>0, "STOP: TRAILING CROSSWALK ROWS OUTSIDE LOOKUP RANGE (RESIZE CROSSWALK)", IF(COUNTA($A:$I)-COUNTA($A$1:$I$9)>0, "STOP: TRAILING INVOICE DATA OUTSIDE TABLE RANGE (RESIZE INVOICES)", IF(V4>0, "STOP: TRAILING CUSTOMER SUMMARY ROWS (RESIZE SUMMARY)", IF(NOT(ISNUMBER(V5)), "STOP: CHECK AUDIT SCOPE OR SUMMARY HEADERS", IF(OR(V12<>0, V13<>0, V14<>0, V15<>0), V16, IF(IF(AND(ISNUMBER(W3), ISNUMBER(W4)), OR(INT(W3)<>W3, W3<>0, INT(W4)<>W4, W4<>0), TRUE), "STOP: INVALID AUDIT GUARD INPUTS", IF(EXACT(W15, "NOT TESTED"), "NOT TESTED: ENTER CANDIDATE REPORT READINGS", IF(EXACT(W15, "CHECK INPUTS"), "STOP: NON-NUMERIC, NEGATIVE, OR INCOMPLETE CANDIDATE INPUTS", IF(W14<>0, "STOP: CANDIDATE INVOICE COUNT MISMATCH (" & W5 & " VS " & V5 & "; $" & TEXT(ABS(W12), "0.00") & " SALES GAP)", IF(W12<>0, "STOP: CANDIDATE SALES TOTAL DIFFERS FROM SOURCE BY $" & TEXT(ABS(W12), "0.00"), IF(W10>0, "STOP: CUSTOMER COUNT MISMATCH (" & W7 & " VS " & V7 & "; UNMAPPED NAME VARIANTS SPLIT ACCOUNTS)", IF(W10<0, "STOP: FALSE ENTITY MERGE (" & W7 & " VS " & V7 & " CUSTOMERS; DISTINCT ACCOUNTS COLLAPSED)", IF(W13>0, "STOP: CUSTOMER TOTALS MISROUTED ($" & TEXT(W13, "0.00") & " TOP/MIN SHIFT; CHECK APPROXIMATE OR WRONG ALIAS)", "RECONCILED: CANDIDATE TOTALS AND BOUNDS MATCH CROSSWALK ($" & TEXT(V6, "0.00") & " ACROSS " & V7 & " CUSTOMERS)"))))))))))))))))))

Continue Reading

Email Notifications

Get notified when new Practical Analytics articles are published.

Field notes on table grain, trustworthy KPIs, and AI-assisted data workflows for small and medium businesses. Sent only when a new guide goes live.

No spam or automated drip funnels. Unsubscribe anytime.