Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to avoid making duplications in a certain column in Excel?

Excel, Excel Users

user-image
Question added by Hany Hassanein , Microsoft Trainer , Egyptian Banking Institute
Date Posted: 2013/05/14
Dua'a Sharawi
by Dua'a Sharawi , Financial Analyst , Cairo Amman Bank- Jordan

* Data > Data Validation > Data Validation * in the Settings tab click drop down list under Allow, choose Custom, and then enter this formula “=COUNTIF($A$1:$A$1000,A1)=1” into the Formula box.
(depend in what column and till what row).
*click Error Alert tab, under the Title box, enter “Duplicate Entry”, and enter the proper message in the Error message box.
By this way we prevent duplication just after invalid data is entered

Mohamed Jamsheer
by Mohamed Jamsheer , Head of the department Supply chain, logistics & Warehouse , ETRONIX MIDDLE EAST LLC (CLIKON)

 

Select required cells.

 

1. Select conditional formatting.

 

2. Goto highlight cells rule.

 

3. Select duplicate value.

 

Duplicate values will be highlighted in the selected cells.

 

 

Jimmy Wahyudi Bharata
by Jimmy Wahyudi Bharata , Sr Application Support , Frisian Flag Indonesia

Step by Step, usually I use :

  1. create new column in the left, ex in column A, named FLAG or COUNT
  2. in row2, give formula "=COUNTIF(B:B,B2)"
  3. you can create filter (Menu DATA >> FILTER)
  4. copy to all row in column A
  5. you can monitoring the result, if it show "2" means, duplication

more tip trik you can visit macro excel - tutorial by irajimmy bekatul

Khaled Abdelrehim ACCA DipIFR CMA
by Khaled Abdelrehim ACCA DipIFR CMA , Financial Analysis Assistant General Manager , Khalda Petroleum Company

Adding to the above, we may use data>subtotal> and count at each change

 

1 - Settings tab 2 - drop down list 3 - choose Custom 4 - enter formula “=COUNTIF($A$1:$A$1000,A1)=1” 5 - in the Formula box. *click Error Alert tab 6 - enter “Duplicate Entry”, 7 - enter message. Follow these steps to prevent duplication

Prasanth Prabhakaran
by Prasanth Prabhakaran , Accounts Manager , BRASSWORLD

Sort Your List, select it , choose option Remove Duplicates from Data Tab, select column name and press OK button all duplicate values will be removed by Excel

Mostafa Mohamed Saed Mohamed El wardany
by Mostafa Mohamed Saed Mohamed El wardany , As supply chain –Procurement executive (Section head), CISCM , SCIB PAINTS EGYPT

the conditional format is the easiest way to find the duplication in a certain column

kamran bashir
by kamran bashir , Assistant financial manager , Verage Ltd

I always prefer Conditional formatting to highlight duplicates.This is technique i was advised to use on one the courses i attended.Its used by auditors extensively and also by internal auditors.

Hamza Mostafa
by Hamza Mostafa , Financial Analyst , Gullivers Travel Associate

If you are entering in one column without any blanks, auto complete will show you if there is duplicate. Or you can use conditional formating Or Countif function Or remove duplicates Or you can make a macro which includes one / all the above to cut the steps to one keyboard shortcut only

IBN Eljarah Mohamed Ali ABD ELGADIR
by IBN Eljarah Mohamed Ali ABD ELGADIR , Group Finance Director , Sigma Electric

Any of the above is correct and is depend on the format  type of your column or row (text , numeric or date ), then you can choose the suitable one. by the way conditional format is suitable for both text and date formats.

Tatheer Hasan Syed
by Tatheer Hasan Syed , Senior Executive Supply Chain , FFBL Power Company Limited

Apply conditional formating in a column which will alert you for duplicates entry each time.

More Questions Like This

Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.