Hardware Store Inventory Management: SKU Clean-Up With AI

Export the item list, find duplicates by barcode, supplier part number and normalised description, merge them with a mapping table, fix units of measure and categories, then set a reorder point for each item from lead-time demand plus safety stock. AI can group near-duplicates and audit the reorder numbers, but you approve every merge.

Clean first, then reorder. When one product lives under three SKUs, its sales history is split three ways and each record looks like a slow seller, so any reorder point built on that data comes out too low. Fixing the list changes the demand numbers, which is why the order matters.

Follow me on Instagram@sagnikteaches

Hardware makes this harder than most retail. The same bolt comes in dozens of sizes, finishes and pack counts; the same product arrives from two suppliers under two part numbers; counter staff type descriptions in a hurry; and the till sells screws loose by weight and boxed by the 200. AI is good at the fuzzy matching and the tedious tidying. It does not know that an M8 bolt and an M10 bolt are different products, and left alone it will merge them.

Connect on LinkedInSagnik Bhattacharya

So the rule is simple: AI proposes, a spreadsheet counts, you approve. The running example is an invented independent store with 8,450 item records, six suppliers that deliver twice a week, and a clean-up spread over about two weeks of evenings. Every figure is an illustration.

Subscribe on YouTube@sagnikteaches

Profile the export before you change anything

Back up the till system, then export every item with these columns: item ID, description, barcode, supplier, supplier part number, category, selling unit, cost, price, on-hand quantity, last sold date, and units sold in the last 12 and 24 months. Save the original as read-only and work in a copy, so you can always see what you started with.

Before any AI is involved, count what is wrong. Pivot tables and three or four formulas are enough, and they give you numbers to compare with once the clean-up is finished. The invented store's profile looked like this.

These formulas produce the flag columns, assuming barcode in column C, supplier part number in E, on-hand in J and units sold in 24 months in L. Ask an assistant to adapt them to your own layout, and test each one on ten rows you can check by eye.

Duplicate barcode:    =IF(AND(C2<>"",COUNTIF(C:C,C2)>1),"DUP","")
Missing supplier no.: =IF(E2="","MISSING","")
Archive candidate:    =IF(AND(L2=0,J2<=0),"ARCHIVE?","")

If a column is full of stray spaces, mixed capitals or text stored as numbers, tidy those first. The routine for that is in cleaning messy data, and it saves AI from having to guess whether "BOX " and "box" are the same unit.

The dead-stock row deserves its own pass later: finding dead stock and slow movers with AI covers what to do with the 520 items that are sitting on shelves unsold.

Three duplicate signals, strongest first

1. The same barcode on two or more items. A manufacturer's barcode identifies one product in one pack size, so two records carrying it are almost always duplicates. The exception is a store that sells one product both singly and by the box and gave each its own SKU; those need a unit conversion, not a merge. In the example, 212 barcodes covered 468 records. Keep the record with the most sales history, move the others' sales and stock onto it, and archive the rest. That removed 256 records.
2. The same supplier part number from the same supplier. This catches duplicates with no barcode or with an in-house one. Sort by supplier, then part number, and look for runs. Watch for part numbers a supplier reuses across pack sizes, and check the description and the cost before merging. After the barcode merges, 190 sets of two or three records remained, and merging them removed 235 more.
3. The same product described three ways. This is where AI earns its place. "SCREW WOOD 4X40 ZP BOX200", "Wood screw 4 x 40mm zinc (200)" and "4.0x40 csk wood screws zinc pl 200pk" are one product, and no formula will see it.

Do the first two with formulas, because they are exact and free. Keep the AI for the third, where fuzziness is the problem. Sending all 8,450 rows to an assistant because it is easy would give you slower and less reliable matches than the formulas give you on the first two signals.

Naming rules that survive a busy counter

A naming rule is what lets two people write the same item the same way. Put the attributes in a fixed order (type, detail, size, finish, pack), use the same units everywhere, and write for the person searching at the till, not for the person typing the record. The three messy records above become one:

The more useful half of the rule says what must match before two records can be called the same product. Give this list to the assistant as a hard constraint, because it is the part it gets wrong unprompted.

• Size: diameter, length, gauge and thread. A 4 x 40 screw and a 4 x 45 screw are different products, and so are M8 and M10 bolts.
• Material and finish: zinc, stainless, galvanised, black, brass.
• Head, drive or fitting type: countersunk or pan head, Phillips or Torx drive, end-feed or solder-ring copper fittings.
• Hand or direction: left or right thread, left or right hinge.
• Pack size: a box of 100 and a box of 200 are separate records that share a base product.
• Colour or shade, for paint, sealant and fillers.
• Brand, only where customers choose by brand, such as power-tool batteries and paint.

Letting AI group the near-duplicates

Take only the records left after the exact matches, sort them by supplier and then category so likely twins sit next to each other, and send about 200 rows at a time. Long pastes make assistants skim, and short sorted batches keep the comparison local.

Below are item records from a hardware store, sorted by supplier and category. Group records that may be the SAME product under different descriptions. Two records are the same only if size, length, thread, material, finish, head or drive type, hand, pack size and colour all match; brand must also match for power tools, batteries and paint. For each group return: the group number, the item IDs, a proposed master name in the pattern "Type, detail, size, finish, pack", the ID to keep (the one with most units sold in 24 months), and a confidence of HIGH, MEDIUM or LOW with the reason. If two records differ in any listed attribute, do not group them. If a description leaves an attribute out, write UNKNOWN and set the confidence to LOW. Do not merge anything yourself.

Columns: ID | description | barcode | supplier part no. | units sold in 24 months
[paste about 200 rows]

A shortened result might read like this (illustrative):

Group 41. IDs 10231, 10987, 14402. Master: "Wood screw, countersunk, 4.0 x 40 mm, zinc, box of 200". Keep 10231 (310 units sold). HIGH: size, head type, finish and pack match in all three, and two carry the same part number, typed with and without the supplier's prefix.

Group 42. IDs 12005, 12006. Master: "Copper elbow, 15 mm". Keep 12005. HIGH: same size, same supplier, similar descriptions.

Group 43. IDs 13310, 13311. Master: "Silicone sealant, 310 ml". Keep 13310. LOW: colour UNKNOWN on one record.

Group 41 is the kind of result you want. Group 42 is wrong, and confidently so: one record is an end-feed elbow and the other a solder-ring elbow, which are different fittings, and the description of one of them did not say. Group 43 is correctly marked LOW, because a clear and a white sealant must not be merged. That is why the confidence label is a guide to where to look first, not a verdict. In the example the assistant proposed 410 groups, and the owner approved 290 (removing 355 records) and rejected 120 where something on the attribute list differed or could not be confirmed. At about a minute a group, that review took roughly seven hours, so give it two evenings, not one.

Two habits keep the review honest. Check every group that is not HIGH by eye, and spot-check one in five of the HIGH ones against the shelf or the supplier's catalogue. And run a second pass on the proposed master names alone, sorted alphabetically, because duplicates sitting under different suppliers fall into different batches and the first pass never sees them side by side.

Record every approved merge in a mapping table: old ID, master ID, old barcode, reason, who approved it and when. That table is your undo button, and it is how you trace an old sale or a supplier invoice that still quotes the retired record.

Units of measure: each, box, metre and kilo

Hardware sells the same stock in several units, and a record that mixes them produces nonsense reorder points: demand counted in boxes, orders placed in cases, stock counted in singles. Give every product one base unit, which is the smallest thing the till sells. Record each supplier pack as a multiple of it, and keep the selling unit and the ordering unit in separate columns.

Two consequences follow. Demand is always counted in the base unit and orders are always placed in supplier packs, so the reorder sheet needs a pack-size column. And a mix-up costs real money: if the nail bin sold 320 times in 100 g steps, that is 32 kg, or 6.4 bags. A sheet that reads "320 sold" as 320 bags would order 1,600 kg, fifty times too much. This is the commonest way a reorder calculation goes wrong in a hardware store, and no amount of clever AI fixes it if the units on the record are muddled.

A category tree you can keep

Categories exist so people can find things and so reports mean something. Three levels are enough: department, category, subcategory. Every item sits in exactly one place, there is no "Misc", and nobody adds a category without the owner agreeing. A slice of the invented store's tree:

Let the assistant file the items, but only into the list you give it.

Assign each item below to exactly one category path from this list. Use only paths from the list. If nothing fits, write NEEDS NEW CATEGORY and do not invent one. If two paths could fit, choose the one a customer would look in first and say which other you considered.

Category list: [paste the tree as Department > Category > Subcategory]
Items: ID | master name

Typical slips (illustrative): a masonry drill bit filed under hand tools when the tree has power tool accessories; a "frost fleece, 10 m" filed under garden tools instead of seasonal; and a key blank rightly marked NEEDS NEW CATEGORY because the tree has nowhere for key cutting. Take 50 items at random and check them. If more than a handful are wrong, add two or three example items to the category list and run the batch again.

Reorder points from real demand: five items

With a clean list, an ordinary item's reorder point is the demand you expect while waiting for the delivery, plus a buffer. Reorder point = average daily demand x lead time in selling days + safety stock. A simple safety stock is the gap between a busy day and an average day, times the lead time: (busy-day demand - average daily demand) x lead time. Take the busy day from your busiest week in the last 12 months, divided by the selling days in a week, not from the single busiest day, which may be a one-off. Lead time is selling days from placing the order to having stock on the shelf, using the worst case you have seen lately. For the general method, see using AI to set reorder points and prevent stockouts. The invented store trades six days a week, 312 selling days a year.

In a spreadsheet it is one line per item. With average daily demand in column F, busiest-week daily demand in G and lead time in H, ask an assistant to adapt this to your own columns, then check three rows against the table below by hand.

=ROUNDUP(F2*H2+(G2-F2)*H2,0)

The two parts add up to busy-day demand x lead time, so the formula could be shorter. Keeping them apart shows how much of each reorder point is buffer, which is the part worth questioning on slow, expensive lines.

The screws. One box a day, 1.83 on the busiest days, four days to wait: 1.0 x 4 + (1.83 - 1.0) x 4 = 7.3, so 8 boxes. The supplier's case is 10 boxes, so the rule is "order one case when stock reaches 8". With a normal four days of sales the shelf holds about four boxes when the case arrives.

The drill. Nine a year is 0.03 a day, and the formula gives 1.0. A reorder point of 1 means "order when one is left", so order one at a time and never hold more than two: that is $196 of cost tied up in the slowest line, and a customer still finds one on the shelf almost every day.

The frost fleece. A yearly average of 144 rolls over 312 days is 0.46 a day, so demand over the five-day lead time averages 2.3 rolls and a reorder point built on the average alone would be 3. In the first frost week customers buy 30 rolls, five a day, so three rolls last about 0.6 of a day. Adding the busy-week buffer fixes the season but not the rest of the year: the full formula then says 25 all year round, which leaves 25 rolls on the shelf from spring to autumn. Use a seasonal rule instead: 25 for the eight in-season weeks, 0 outside them, with the start date written in the calendar.

The silicone sealant. The system shows 480 tubes sold in 52 weeks, but the shelf was empty for five of those weeks. Dividing by 52 gives 9.2 a week. Dividing by the 47 weeks it was actually on sale gives 10.2, about 11% higher. Stock-outs hide demand, so leave zero-stock weeks out of the average and tell the assistant to do the same.

The special-order taps. Items the store only orders for a customer, such as kitchen taps chosen from a catalogue, get no reorder point at all. A reorder point on an item that is only ever ordered for a customer creates stock nobody asked for.

Two practical points before you load any of this. Start with the items that matter most: rank by sales value and set the top 200 first, as in the ABC analysis of SKUs and customers tutorial. And count those items on the shelf before you trust the system's on-hand figures, since a reorder point built on a quantity of 14 is useless when the shelf holds 2; why a stock count and the system disagree explains the usual causes.

Letting AI audit the reorder points

This is the right job for an assistant: not calculating the numbers, but reading a finished table and pointing at rows that look wrong. Keep the arithmetic in the spreadsheet, export the result, and ask for exceptions only.

Do not recalculate anything. Below is a table of reorder points I calculated in a spreadsheet. List the rows where the numbers look wrong or risky. For each row give the SKU, the issue and the check I should make. Look for: blank or zero lead times; a reorder point higher than the shelf can hold; items with no sales in 24 months but a reorder point above zero; more than 4 weeks out of stock in the last year; a supplier minimum order of more than 26 weeks of demand; negative on-hand quantities; and pack sizes that look inconsistent with the units sold. Do not suggest new numbers.

Columns: SKU | description | units sold 12 months | weeks out of stock | average weekly demand | lead time days | safety stock | reorder point | supplier pack | supplier minimum order | on-hand | shelf capacity
[paste rows]

A plausible result (illustrative):

What the audit cannot do is just as important. It does not know a line is seasonal unless you say so, it does not know a supplier has promised faster delivery, and it can miss rows. So sort the sheet yourself by the same columns as a second look, and rerun the audit on every new export. It takes minutes, and it catches the rows where something has changed since last quarter.

Where the numbers go in the till system

Check what your own system offers before you consider switching. Lightspeed Retail's inventory page lists reorder points or ideal minimum and maximum stock levels, low-stock alerts, product variants, purchase orders, CSV imports and smart reorder recommendations. Square for Retail's pricing page puts inventory management, stock intake, and vendor and purchase-order management on its Plus and Premium plans, and describes low-stock alerts and purchase orders imported from a CSV or Excel file (both checked October 2026). Shopify's Stocky app stopped working on 31 August 2026; purchase orders now live in Shopify admin and Shopify points merchants to its Sidekick assistant for reorder suggestions, though Stocky's purchase-order history cannot be imported.

Whatever you run, three questions decide whether the clean-up sticks. Can you import item changes from a CSV file? Does a merge keep the retired item's sales history? Can one item carry several barcodes? The answers differ between systems, so ask your vendor and write the answers down before you merge anything. If the system has no reorder-point field, keep the reorder table in the spreadsheet and review it weekly. For a comparison of what to look for in a system, see hardware store POS systems: which features matter and where AI fits.

A two-week plan and what the clean-up changed

The invented store spread the work over about two weeks of evenings and one weekend, roughly 24 hours in all. At $20 an hour of the owner's or a part-timer's time that is about $480, a figure to set against the stock-outs and wasted counter time you expect it to remove.

The effect on the item count, step by step:

That is 2,226 records fewer, 26% of the list, and every one is traceable through the mapping table or the archive. Three precautions make the import safe. Import 20 items first and search for them at the till as a customer would. Load the rest in category batches, never on a busy trading day. And if the store also sells online, check that the web shop's product links do not point at SKUs you have just merged.

To check the work held, repeat the profile each month. Duplicate barcodes should stay at zero, items with no category at zero, and the list of audit exceptions should get shorter. Ask counter staff to tally "item not found" searches for a fortnight before and after, and count stock-outs on your top 200 items each month. The list will drift again as new items arrive, so give one person the job of approving new records against the naming rule. A clean list that nobody guards drifts back to the old state.

Questions hardware owners ask about SKU clean-ups

How many SKUs should a hardware store have?

There is no right number. Test each active SKU instead: is it a distinct product, does it have a supplier source, and has it sold recently or is it kept for a reason such as a special-order line or safety stock? In the invented example 8,450 records became 6,224 active ones, but your count depends on your range and how long the list has gone untended.

Should I delete duplicate SKUs or archive them?

Archive them and keep the mapping table of old SKU to master SKU. Many systems will not let you delete an item with sales history, and you may need the old record to trace an old sale or a supplier invoice. If your system allows several barcodes on one item, move the old barcodes across so the scanner still finds the product.

Can ChatGPT clean a 10,000-row item list in one go?

Not reliably. Long lists strain the context window and accuracy drops. Do the exact checks with spreadsheet formulas, then send only the fuzzy candidates in batches of about 200 rows, grouped by supplier or category, ask for structured output, and spot-check each batch. The assistant is for judgement calls, not for counting rows.

How often should reorder points be reviewed?

There is no universal rhythm. A sensible start is quarterly for your fastest sellers, yearly for slow lines, and straight after any change in a supplier's lead time or a new season. Rerun the AI audit on each new export, because it costs minutes and catches the rows where a change has made last quarter's number wrong.

Further reads

• AI for a Hardware Store: Can It Answer "What Do I Need for This Job?" — Counter staff answering 'what do I need for this job?' from clean data.
• How to Run a Stocktake Faster With AI and a Phone Scanner — Count the fast sellers quickly so stock figures can be trusted.
• Best AI Inventory Tools for Small Retailers Compared — Dedicated inventory tools compared if spreadsheets stop being enough.
• Tyre Stock Excel Sheet: Track Sizes and Reorder Points With AI — Another stock range with many sizes, tracked by reorder point.
• AI in Retail Commerce: Click-and-Collect for a Single Local Shop — Set up click-and-collect in the system you already use: a stock buffer rule with numbers, four order messages and a shelf-ordered pick list.
• Hardware Store Marketing Ideas: Trade Accounts and DIY Weekends With AI — Write a trade-account pitch, plan DIY demo weekends with project sheets, and time seasonal stock posts from last year's sales, using AI you check yourself.
• Game Store Inventory Management: Preorders and Trade-Ins With AI — Three ledgers for a games shop: a preorder deposit log, a singles pricing rule and a trade-in buy list, with AI drafting listings and customer replies.
• Music Store Inventory Management: Used Gear and Trade-Ins With AI — One row per instrument, serials stored as text, a trade-in pricing sum and AI-drafted condition notes and listings, all in a spreadsheet a small shop can run.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Lightspeed Retail's inventory management page and Square for Retail's pricing page (checked October 2026) and Shopify's notice that Stocky stopped on 31 August 2026. Every store figure in this tutorial is invented for illustration.