5.1 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 |
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 |
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).