Did that payment land on the right client?
Fourteen bank payer names matched against the client master on name and city together. Matching on the name alone finds two matches that score beautifully and are the wrong company.
Finance Starter Fuzzy Lookup free
After you install, this is the model to open.
Match the Invoices to the Client List
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Matched
- 12 of 14 bank payer names joined on name and city together
- Correct refusals
- 2 the rows with no true counterpart come back empty
- Weakest true match
- 0.643 Rossway Labs against Rossway Laboratories
- The threshold
- 0.60 with two match columns, a 0.50 score is half a match
A client master on one side and a bank feed on the other, fourteen rows each, and the feed is deliberately out of order so no line-by-line comparison is possible. Two match columns are set on each side, the name and the city, and when you match on more than one column the score is the average across them. Click Run at the prefilled threshold of 0.60: twelve rows come back matched and two come back empty.
All twelve are right and both refusals are right. Now do the run that shows you why the city is in there at all. Set both match column boxes back to 1, so the join runs on the name alone, and drop the threshold to 0.50. You get twelve matches again, but two of them are wrong. Bright Mile Ltd, who you act for in Leeds, is matched to a Bright Mile in Manchester at 0.699, and Merrow Fitness of London is matched to a Merrow Fitness in Leeds at a perfect 1.000.
A score of 1.000 guarantees nothing except that two strings are identical, and two companies with the same name in different cities are the single most common way a receipt lands on the wrong ledger. The name-only run also loses two real matches: Halcyon Group against Halcyon Grp, and Rossway Labs against Rossway Laboratories, because a shortened word and a lengthened one are both bad news for a token match.
There is a third run worth doing, and it is a trap rather than a suggestion. Keep both match columns and drop the threshold to 0.50: now you get fourteen matches, and the two extras score exactly 0.500. Bright Mile Ltd pairs with Halcyon Grp and Merrow Fitness pairs with Nine Elms Retail Ltd. Those pairs share a city and nothing else, and a perfect 1.000 on one column averaged with a 0.000 on the other lands precisely on the default threshold.
With two match columns, 0.50 is the score of a row that matched on half of itself, so raise the threshold whenever you add a column. The cost of running at 0.60 is real and small: Halcyon squeaks through at 0.654 and Rossway at 0.643, and anything genuinely below that needs a human. To use your own feed, put the master under the Client and City headings, the feed under the Payer name and City on the mandate headings, widen both ranges to match, and keep the receipts outside the range you point the right side at.
The model
It arrives on a tab called Template: Match the Invoices to the Client List, carrying these columns:
- Client
- City
- Payer name
- City on the mandate
- Amount ($)
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- What are the odds the carry is zero?What Does Carry Actually Look Like?
- Do overruns or write-offs cost you more?Will the Fee Book Cover the Practice?
- Which invoice should you check before it goes out?Which Invoices Do Not Look Right?
- What is your diversification rule costing you?Which Deals Can We Actually Fund?
- Which of your cost lines are really one risk?Which Two Costs Move Together?
- What does half a point of fee cost the investor?What Fee Does the Fund Need to Clear the Hurdle?
Every model like this one, and the method behind them: Statistics in Google Sheets.