Does this delivery note match a supplier on file?
Paste two lists that spell names differently and get back which rows belong together. A sixteen-row supplier master against a sixteen-row goods-received log, joined on near matches. Fourteen pairs come back, the two that should not match do not, and raising the threshold to be safe loses three real suppliers.
Operations Starter Fuzzy Lookup free
After you install, this is the model to open.
Which Rows in Two Vendor Lists Are the Same Supplier?
- 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
- 14 of 16 delivery notes tied to a supplier code, all 14 correct
- Refused
- 2 both are genuinely different companies, not typos
- Hardest catch
- 0.68 Delaunay Castings S.A. against Delaunay Castings SA
A supplier master on the left and a goods-received log on the right, sixteen rows each, with the right-hand block deliberately out of order so no row-by-row line-up is possible. The two match column settings are the load-bearing part of this template: they are set to the second column of each table, the names, because the panel default is the first column, which here is the supplier code against the goods-received reference, and matching those scores every pair at zero.
Click Run and every master row comes back with its single best match and a similarity score between 0 and 1. Fourteen match at the default threshold of 0.50, and all fourteen are correct: three typos, several suffix drifts, and two names entered back to front. Matching works on whole words rather than letters, which is why the reversals cost nothing at all, Hollingsworth Fasteners against Fasteners Hollingsworth scoring a clean 1.000, and why a single transposed letter barely registers, Prentice Rubber Wroks scoring 0.977.
The harder half of the job is what it refuses. Quantock Wire Products and Ravensdale Coatings come back unmatched, and they should, because Cadogan Wire Products and Thorne Coatings Group are different companies that happen to sit in the same trade. A join that matches everything is not a good join. Now look at the two lowest passing scores, because they say exactly where the matching is weak.
Delaunay Castings S.A. against Delaunay Castings SA scores 0.682, and Ostrander Steel & Wire against Ostrander Steel and Wire scores 0.692. Both are obviously the same supplier to a human. The periods split S.A. into two single letters that share nothing with the token SA, and writing an ampersand out as the word "and" adds a whole word that has nothing on the other side to match.
Compare Meiring & Sons Foundry against Sons Meiring Foundry, which is reversed and has lost its ampersand entirely: that scores a perfect 1.000, because a bare ampersand is dropped as punctuation while "and" is a word. Raise the threshold to 0.80 to be safe and you lose exactly those two plus Elsworth Belting Ltd at 0.721, three real suppliers gone and not one bad match prevented.
The fix for all of it is upstream: strip periods and replace written-out ampersands in your data rather than chasing the dial. Leave matches per row at 1 until you trust the scores; raise it to 3 when you want to see the runners-up before accepting anything. To use your own lists, paste each one under its own headers with the master on the left and the received log on the right, point the two ranges at them, set both match columns to the name column, and widen the ranges.
The model
It arrives on a tab called Template: Which Rows in Two Vendor Lists Are the Same Supplier?, carrying these columns:
- Supplier code
- Supplier name
- GRN ref
- Name on the delivery note
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 does an unreliable supplier cost you in stock?Safety Stock for a Plant That Cannot Stop
- Is the line running on a cycle nobody scheduled?Does Our Output Repeat on a Cycle?
- Is a flat daily plan good enough?The Season Repeats: Forecast It
- Is your contingency big enough if delivery slips?What If the Supplier Misses?
- How many tickets must you sell to break even?What Ticket Price Covers the Room?
- Is it the venue or the time of year?Venue and Month: What Drives Attendance?
Every model like this one, and the method behind them: Statistics in Google Sheets.