
Two people can have the same name. One customer can have two orders on the same day. Before cleaning a spreadsheet, decide what a repeated value actually means. A duplicate flag is a cue to check the records against the original source.
This walkthrough uses a fictional order log. The same technique works for class registers, sign-up lists and inventory files.
Define the columns that identify a record
Suppose each row represents an order and each order has a unique ID. Repeat customer names are expected. An order ID that appears twice is worth reviewing, because either the order was entered twice or the sheet records separate items from the same order. Check what one row represents before choosing a rule.
Enter this data in A1:D6. The day values represent consecutive days in one sample month.
| Order ID | Customer | Day | Amount |
|---|---|---|---|
| ORD-101 | Alex | 12 | 28 |
| ORD-102 | Maya | 12 | 15 |
| ORD-101 | Alex | 12 | 28 |
| ORD-103 | Alex | 12 | 28 |
| ORD-104 | Sam | 13 | 20 |
Flag repeated order IDs
Put ID check in E1. Enter this formula in E2 and copy it to E6:
=IF(A2="","Missing ID",IF(COUNTIF($A$2:$A$6,A2)>1,"Review ID",""))
Rows 2 and 4 should show Review ID. Row 5 is an important test. Its customer, day and amount match the entries for ORD-101, but its ID is ORD-103. If the source lists two orders, keep both.
The dollar signs fix the lookup range. When you add new orders to this practice sheet, extend the formula range so it includes the new rows.
Compare more than one field when IDs are missing
If you receive a file without order IDs, look for rows that match across several fields. Put Possible match in F1 and enter this in F2:
=IF(OR(B2="",C2="",D2=""),"Incomplete record",IF(COUNTIFS($B$2:$B$6,B2,$C$2:$C$6,C2,$D$2:$D$6,D2)>1,"Review match",""))
Copy the formula through F6. It flags rows 2, 4 and 5 because all three share the same customer, day and amount. Comparing that result with the ID check shows the trade-off: the second method finds more candidates and requires extra evidence before you remove any record. In a real order log, compare the full date so transactions on the 12th of different months do not match.
Review the flagged rows and reconcile the result
- Make a copy of the source sheet and filter the copy by the review column.
- Compare the flagged entries with the original order receipts or export. Check whether the sheet uses one row per order or one row per item.
- Record your decision in a separate column. For example, write “Repeated entry confirmed” or “Separate order confirmed.”
- When a duplicate is confirmed, remove the repeated row from the working copy and compare the resulting total with the source.
The practice data totals 119. If the source confirms that ORD-101 was entered twice, removing one 28-unit entry brings the total to 91. If you also remove ORD-103 based on the customer, day and amount check alone, the total drops to 63. The reconciliation makes that extra removal visible.
Formula reference — Microsoft Support guide to counting cells by criteria