Vendor Master Merge

Loading the puzzle board…

Loading your Lab profile…

Pass the puzzle

Send this one to a friend.

The friend link carries Dirty Data Lab™ referral attribution, so referred signups can be measured separately from newsletter and ad traffic.

Email a friend

Take the habit into Google Sheets

Practice here. Preflight real sheets with CTS Guard.

Clean That Sheet Guard checks the active Google Sheet for duplicates, missing data, formatting issues and other handoff problems before you share or import it.

Get CTS Guard

# spreadsheet-help· new

Ben LewisAccounting · just now

Can you help me clean up this vendor master before I use it for this week’s payables run? I’ve got duplicate vendors and a few fields that don’t agree.

Accounting has two versions of several vendors. The newest record wins, but a blank field on the newest row must inherit the last known nonblank value from the older match.

What I need:

Normalize vendor_id, vendor names, city/state, tax IDs and phone numbers.
Format tax IDs as NN-NNNNNNN and phones as NNN-NNN-NNNN.
Normalize updated_at to YYYY-MM-DD and status to Active or Inactive.
For duplicate vendor_id values, keep the newest updated_at row.
If the newest duplicate has a blank field, fill it from the older matching vendor.
Sort by vendor_id ascending.

Spreadsheet attached · deadline starts when you press Start challenge