Cleaning data: duplicates and blanks

You exported six months of sales from 1C into Excel: 18,420 rows. The total is 2,500 million so'm, yet the standard 1C report shows 2,462.5 million. Where does the 37.5 million so'm gap come from? The data was exported twice: the date ranges overlapped and one day was written twice. Analyse such data uncleaned and ABC, turnover and forecasts are all built on that error. Cleaning is tedious, but the time and money it saves are worth more.

The order of work

1. Keep the original file and work in a copy.
2. Remove extra spaces (TRIM) and unify letter case: "Cable " and "Cable" are two different values to a system.
3. Find repeated rows.
4. Deal with blank fields.
5. Flag values that stand far out, for example a quantity of 10,000 instead of 10.
6. Compare the total with the 1C report: this is the control total.

Duplicates: what can be deleted

Duplicates usually come from three places: exports with overlapping dates, several files or branches pasted into one table, and a document entered twice. Define the key of a row: document number and line number. 312 rows match in every field: document, line, date, item, quantity. Delete them and 18,420 − 312 = 18,108 rows remain, while the total is 2,500 − 37.5 = 2,462.5 million so'm: it agrees with the 1C report. That is the sign the cleaning went right.

Be careful: two identical lines for one item within the same minute, in two different documents, are not a duplicate but two sales. If you delete by item, quantity and time alone, you lose real sales. First mark them with COUNTIFS (duplicate = 2 or more), then delete.

A blank is not a zero

• Category blank: 540 rows (2.9 percent). Pull it from the item list by item code (VLOOKUP). For the 42 not found write "Unknown" and show their number in the report.
• Cost blank: 96 rows. Do not put in zero! If revenue is 400,000 so'm and the cost is zero, profit looks like 100 percent, whereas with a cost of 75 percent it is 25. Over 96 rows revenue is 96 × 400,000 = 38.4 million so'm, the real profit 9.6 million, and with a zero cost 38.4 million: 28.8 million so'm of false profit. Take the price of the last receipt or flag the row and leave it out of the margin calculation.
• Quantity blank: check against the document; if you cannot find it, exclude the row from the analysis and note it in the log.

A negative quantity (410 rows here) is not an error but returned goods. Do not delete it.

Number or text?

Numbers from 1C often land in Excel as text: "1 200,50" contains a space and its decimal sign does not match your Excel settings. The sign: the cell is left-aligned and the total is smaller than expected. The check: ISNUMBER(A2). The fix: strip the space with SUBSTITUTE and apply VALUE. The same goes for dates: if "12.03.2026" is text, you cannot group by month.

The cleaning log

Write one line for every rule: what was done, how many rows it touched and its effect on the total.

• Duplicates (document + line): 312 rows, −37.5 million so'm.
• Category filled in: 498 rows, "Unknown" remains: 42.
• Cost taken from the last receipt price: 96 rows.
• Numbers stored as text corrected: 1,240 cells.
• Returns (negative quantity): 410 rows, not changed.

With a log you can apply the same rules six months later and compare results. To repeat the work by the same rules instead of by hand, use Power Query in Excel or Google Sheets formulas.

Conclusion

The quality of analysis cannot exceed the quality of data. Remove duplicates by key first, do not turn a blank into a zero, write each step in a log and reconcile the total with the source. Cleaning fixes errors on paper, not on the shelf. If the balance itself is wrong, no cleaning helps: CountLab establishes the real balance through a stock count and runs the analysis on verified data.