About Services Tools Blog Contact us

Supplier Sheet Normalisation

Cleans each cell of a supplier spreadsheet as it comes in and puts brand names onto one spelling. A preview shows every change before anything is saved.

  • Data Merge & Cleaning
  • Footwear retail
  • Tool
  • Productised
  • Local/private
  • 2026

The problem

Supplier cells arrive with stray formatting codes, fractions typed as symbols and values outside the shop's allowed lists. The same brand is also stored in capitals when it comes from a label and in title case when an operator types it. So filters split one brand into two.

What it does

For each column you pick a cleaner and see the result beside the original cell before saving. The default cleaner removes stray formatting, straightens quotation marks and turns a fraction symbol into a decimal. Others force a value onto an allowed list. A separate step groups brand names that differ only by capitals and rewrites them to the shop's spelling, or to the spelling most rows already use.

Outcomes

  • A fraction size such as a half becomes a decimal before saving.
  • A value outside an allowed list is forced onto it or left blank.
  • Each brand has one spelling across the incoming rows.
  • The brand clean-up shows its changes without saving them until approved.

Simplified mockup

Interactive preview with sample data. Nothing is sent to a server.

UK size column

Not saved
Supplier cell row 18
<b>5½</b>

As the sheet shows it

5½

A fraction glyph and a stray tag. The column is mapped to uk_size.

Cleaner for this column

Preview, beside the original

5.5

Markup stripped. The fraction glyph becomes a decimal. Nothing is reworded.

Before save
The original cell stays in the sheet until you save.

Width fitting

Allowed values for this column
E EE EEE 4E

Nearest value we use. An exact match is kept. Anything with no neighbour is blanked rather than invented.

Three supplier cells
RowSupplier cellOn the listWill write
42 Extra wide no 4E nearest
43 EEE yes EEE kept
44 super-narrow no (blank) no neighbour
Preview only until you force the list.

One spelling each

Rows in the report
67
Not written yet
Pairs
3
Case differs only
Already matching
11
Left as they are
Report, before Apply
As stagedRowsSettles onWhy
NORTHMILL 42 Northmill Shop list would change
PEBBLE LANE 18 Pebble Lane Shop list would change
harbour thread 7 Harbour Thread Shop list would change
Calder & Pine 11 Calder & Pine Already the shop spelling matches
67 rows would change. Apply writes them.

How it works

  1. Pick a cleaner for the column from the mapping list.
  2. Preview the cleaned value beside the original cell.
  3. Leave the field blank when the cleaner returns nothing.
  4. Group brand spellings that differ only by capital letters.
  5. Prefer the shop brand list, otherwise use the spelling found in the most rows.
  6. Show the proposed changes first and save them only when a person approves.

Pipeline

flowchart LR
  A[Cell] --> B[Cleaner]
  B --> C[Preview]
  C --> D[Save]
  E[Brands] --> F[Group]
  F --> G[Report]
  G --> H[Apply]