5.1 Duplicates
Quick answer: the short, self-contained version of this lesson is the guide How to Remove Duplicates in Excel (Data Tab).
Flag duplicates with COUNTIF with example — in 30 seconds
What: A growing range like $A$2:A2 counts how many times an ID has appeared so far. More than 1 means a repeat.
Why: Deleting duplicates blindly can delete real data. A flag column lets you check first, then remove.
Tiny example: BLK-1001 appears in rows 2 and 4. Row 4 is flagged Duplicate. Total flagged: 2.
Flag duplicates with COUNTIF with example — 30 सेकंदात
काय: $A$2:A2 सारखी वाढत जाणारी range एखादा ID आत्तापर्यंत किती वेळा आला ते मोजते. 1 पेक्षा जास्त म्हणजे repeat.
का: Duplicates आंधळेपणाने delete केले तर खरा data पण जाऊ शकतो. Flag column मुळे आधी check करता येतं, मग काढता येतं.
छोटं उदाहरण: BLK-1001 rows 2 आणि 4 मध्ये आहे. Row 4 ला Duplicate flag लागतो. एकूण flagged: 2.
Flag duplicates with COUNTIF with example — 30 सेकंड में
क्या: $A$2:A2 जैसी बढ़ती range गिनती है कि कोई ID अब तक कितनी बार आया. 1 से ज़्यादा यानी repeat.
क्यों: Duplicates बिना देखे delete करने पर असली data भी जा सकता है. Flag column से पहले check कर सकते हैं, फिर हटा सकते हैं.
छोटा उदाहरण: BLK-1001 rows 2 और 4 में है. Row 4 पर Duplicate flag लगता है. कुल flagged: 2.
First look at the data (before → after):
Expected result — Flag column B
| A: Order ID | B: Flag |
|---|---|
| BLK-1001 | First |
| BLK-1002 | First |
| BLK-1001 | Duplicate |
| AMN-1003 | First |
| BLK-1002 | Duplicate |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on duplicates.
Before table and formula as text
Before — Export with repeated IDs
| Row | A: Order ID |
|---|---|
| 2 | BLK-1001 |
| 3 | BLK-1002 |
| 4 | BLK-1001 |
| 5 | AMN-1003 |
| 6 | BLK-1002 |
About this data: Demo table only — not real customer or company data. Built to practise duplicates.
Formula / click path
B2: =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First") → copy down
Do it step by step:
- Click B2.
- Type
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First"). - Only the first $A$2 is locked. The end of the range grows as you copy down.
- Copy down, then filter column B for Duplicate.
- Check those rows, then delete them or use Data › Remove Duplicates.
स्टेप बाय स्टेप करून बघा:
- B2 वर click करा.
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First")type करा.- फक्त पहिला $A$2 lock आहे. Copy करताना range चा शेवट वाढत जातो.
- खाली copy करा, मग column B मध्ये Duplicate filter करा.
- त्या rows check करा, मग delete करा किंवा Data › Remove Duplicates वापरा.
स्टेप बाय स्टेप करके देखें:
- B2 पर click करें.
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First")type करें.- सिर्फ़ पहला $A$2 lock है. Copy करने पर range का आख़िरी हिस्सा बढ़ता जाता है.
- नीचे copy करें, फिर column B में Duplicate filter करें.
- उन rows को check करें, फिर delete करें या Data › Remove Duplicates इस्तेमाल करें.
Before
| Order ID | City | Product | Amount |
|---|---|---|---|
| BLK-3001 | Pune | Gokul Cow Milk 500 ml | 32 |
| BLK-3002 | Nashik | Nashik Grapes 500 g | 90 |
| BLK-3001 | Pune | Gokul Cow Milk 500 ml | 32 |
| BLK-3003 | Nagpur | Nagpur Oranges 1 kg | 120 |
| BLK-3002 | Nashik | Nashik Grapes 500 g | 90 |
About this data: Made-up rows so you can copy the pattern for duplicates. Currencies may use ₹, £, € or $.
After
| Order ID | City | Product | Amount |
|---|---|---|---|
| BLK-3001 | Pune | Gokul Cow Milk 500 ml | 32 |
| BLK-3002 | Nashik | Nashik Grapes 500 g | 90 |
| BLK-3003 | Nagpur | Nagpur Oranges 1 kg | 120 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
Steps in Excel
- See them first: select Order IDs › Home › Styles › Conditional Formatting › Highlight Cells Rules › Duplicate Values.
- Flag with a formula: in E2
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First")– the expanding range$A$2:A2marks the 2nd, 3rd… copies only. Filter on "Duplicate" to review. - Remove: click inside the data › Data › Data Tools › Remove Duplicates › tick only the columns that define a duplicate (here Order ID; or all columns for fully identical rows) › OK. Excel reports how many were removed – here 2 duplicate values found and removed; 3 unique values remain.
- Formula way (Microsoft 365 / Excel 2021+):
=UNIQUE(A1:D6)returns the distinct rows in a new place;=UNIQUE(B2:B6)gives the distinct city list.
Ravindra Bagale's Tip
In Remove Duplicates, many students leave all columns ticked or tick the wrong columns. If the same customer placed two different orders, they are not duplicates! First decide what counts as a duplicate (Order ID? Order ID + Product?), and review with a COUNTIF flag before removing – after Remove Duplicates, you can't get the rows back later.
Ravindra Bagale's Tip – मराठी
Remove Duplicates मध्ये बरेच students सगळे columns tick ठेवतात किंवा चुकीचे columns tick करतात. एकाच customer ने दोन वेगळे orders केले तर ते duplicate नाहीत! Duplicate कशाला म्हणायचं (Order ID? Order ID + Product?) हे आधी ठरवा, आणि remove करण्याआधी COUNTIF flag ने review करा – Remove Duplicates नंतर rows परत मिळत नाहीत.
Ravindra Bagale's Tip – हिंदी
Remove Duplicates में बहुत से students सारे columns tick रहने देते हैं या गलत columns tick कर देते हैं. एक ही customer ने दो अलग orders किए तो वे duplicate नहीं हैं! पहले तय करो कि duplicate किसे कहेंगे (Order ID? Order ID + Product?), और remove करने से पहले COUNTIF flag से review करो – Remove Duplicates के बाद rows वापस नहीं मिलतीं.
Practice task
In a list of 20 orders with 4 repeated Order IDs, flag duplicates with COUNTIF, remove them with Remove Duplicates, and confirm the new count with =ROWS(UNIQUE(A2:A21)) (Microsoft 365).
Loading practice tasks...
- How many times did P-501 come? (Easy): Count how many times P-501 is in B2:B11.
- Does P-506 repeat? (Easy): Check if P-506 appears more than once in B2:B11 (TRUE or FALSE).
- First or duplicate? (row 4) (Easy): Is B4 a "Duplicate" or "First"? Count only from B2 down to B4.
- First or duplicate? (row 5) (Easy): Is B5 a "Duplicate" or "First"?
- Extra visits of Emma (Medium): How many extra (repeat) rows does P-502 have?
- Number of different patients (Medium): How many different Patient IDs are in B2:B11?
- List of patients (spill) (Medium): Get the list of different Patient IDs from B2:B11.
- List of cities (spill) (Medium): Get the list of different cities from D2:D11.
- How many duplicate rows? (Hard): How many rows are repeats? (all rows minus different patients)
- One row per patient (spill) (Hard): Get the different rows of B2:D11 (3 columns).
- Is the last visit a repeat? (Hard): Is B11 a "Duplicate" or "First"?
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
