In Google sheets, how could you prevent duplicate entry in a specific column? Actually, the Data validation in Google sheets can help you to finish this task.
Prevent duplicate entry in Google sheets with Data validation
Prevent duplicate entry in Microsoft Excel with Kutools for Excel
Prevent duplicate entry in Google sheets with Data validation
Normally, the Data validation in Google sheets can do you a favor, please do as this:
1. Select a list of cells that you want to prevent duplicates, and then click Data > Data validation, see screenshot:
2. In the Data validation dialog box:
(1.) Choose Custom formula is from the drop down list, and enter this formula: =countif(A$2:A2,A2)=1 into the Criteria text box;
(2.) Then select Reject input option from the On invalid data section.
Note: In the above formula, A2 is the start cell of the column that you want to use.
3. Then click Save button, from now on, when duplicate data is entered in that specific column, a warning box will pop out to remind you, see screenshot:
Prevent duplicate entry in Microsoft Excel with Kutools for Excel
In Excel worksheet, you can prevent duplicate entries by using a useful tool-Kutools for Excel, with its Prevent Duplicate feature, you can quickly finish this task in Excel.
Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.Go to Download
Free Trial 30 daysPurchase
PayPal / MyCommerce
After installing Kutools for Excel, please do as this:
1. Select the cells that you want to prevent duplicates, and then click Kutools > Prevent Typing > Prevent Duplicate, see screenshot:
2. And then a prompt box is popped out to remind you if there are Data Validation cells in your selected range, it be removed when applying this feature, see screenshot:
3. Then click Yes button, and a prompt box will pop out as following screenshot shown:
4. Then click OK button, now, when you enter duplicate value in the specified column, a warning box will pop out, see screenshot:
If you have a list of data in a worksheet with unique values and duplicate values, and you do not only want to count the frequency of duplicate values also want to know the order of the occurrence of the duplicate values. In Excel, the COUNTIF function can help you count the duplicate values.
- Count the frequency of duplicates in Excel
- Count the order of occurrence of duplicates in Excel
- Count and Select all duplicate values in a column with Kutools for Excel
- Count occurrences of each duplicate in a column with Kutools for Excel
Count the frequency of duplicates in Excel
In Excel, you can use COUNTIF function to count the duplicates.
Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A$9, A2) (the range $A$2:$A$9 indicates the list of data, and A2 stands the cell you want to count the frequency, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:
Tip: If you want to count the duplicates in the whole Column, use this formula =COUNTIF(A:A, A2) (the Column A indicates column of data, and A2 stands the cell you want to count the frequency, you can change them as you need).
Quickly select and count all duplicate/unique values from a column in Excel
In general, we can remove duplicates from a list easily by Data > Remove Duplicates in Excel. But how to select duplicate/unique values, or count them from a column? And what if selecting or counting duplicate/unique except the first duplicate one? Try Kutools for Excel’s Select Duplicate & Unique Cells utility!
Free Trial 30
Kutools for Excel - Includes more than
300
handy tools for Excel. Full feature free trial30
-day, no credit card required! Get It NowCount the order of occurrence of duplicates in Excel
But if you want to count the order of the occurrence of the duplicates, you can use the following formula.
Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A2,A2) (the range $A$2:$A2 indicates the list of data, and A2 stands the cell you want to count the order, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:
Count and select all duplicates in a column with Kutools for Excel
Sometimes you may want to count and select all duplicates in a specified column. You can get it done easily with Kutools for Excel’s Select Duplicates & Unique Cells utility.
Kutools for Excel - Includes more than
300
handy tools for Excel. Full feature free trial30
-day, no credit card required! Free Trial Now!1. Select the column or list that you will count all duplicates, and click the Kutools > Select > Select Duplicates & Unique Cells.
2. In the opening Select Duplicate & Unique Cells dialog box, check the Duplicates (Except 1st one) option or All duplicates (Including 1st one) option as you need, and click the Ok button.
And then you will see a dialog box comes out and shows how many duplicates are selected, and at the same time duplicates are selected in the specified column.
Note: If you want to count all duplicates including the first one, you need to check the All duplicates (Including 1st one) option in the Select Duplicate & Unique Cells dialog box.
3. Click the OK button.
Kutools for Excel - Includes more than
300
handy tools for Excel. Full feature free trial30
-day, no credit card required! Get It NowCount occurrences of each duplicate in a column with Kutools for Excel
Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch count the occurrences of each items in a column (the Fruited Column in our case), and then delete the duplicate rows based on this column (the Fruit Column) easily as below:
Kutools for Excel - Includes more than
300
handy tools for Excel. Full feature free trial30
-day, no credit card required! Free Trial Now!1. Select the table containing the column where you will count each duplicate, and click Kutools > Content > Advanced Combine Rows.
2. In the Advanced Combine Rows, select the column you will count each duplicate and click Primary Key, next select the column you will put counting results in and click Calculate > Count, and then click the OK button. See screenshot:
And now it has counted the occurrence of each duplicate in the specified column. See screenshot:
Kutools for Excel - Includes more than
300
handy tools for Excel. Full feature free trial30
-day, no credit card required! Get It NowDemo: count duplicate values in a column in Excel by Kutools for Excel
Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!
Relative Articles:
Count merged cells in Excel
Count blank cells or nonblank cells in a range in Excel
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More... Free Download... Purchase...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Read More... Free Download... Purchase...
Oldest First
Sort comments by
Oldest First
Newest First
Comments (57)
No ratings yet. Be the first to rate!
Adnan Khan
about 8 years ago
#12332
This comment was minimized by the moderator on the site
easy and good one :D
Reply
0
0
Priyanka
about 8 years ago
#12542
This comment was minimized by the moderator on the site
Is there any other function or way to calculate the same..?? Because countif() slows down the functioning of the sheet. Please suggest.
Reply
0
0
Amol Chopade Priyanka
about 8 years ago
#12543
This comment was minimized by the moderator on the site
More use of functions and formulas make worksheet slower. There is no method to solve it even i suggest you to use special paste option after using formulas and functions. It will solve your problem. Just use it "Alt+s+e+v". :-)
Reply
0
0
Ankit Priyanka
about 8 years ago
#12544
This comment was minimized by the moderator on the site
contact me @@ if u want stop duplicacy!! * conditional formating duplicate
Reply
0
0
Zana
about 8 years ago
#12654
This comment was minimized by the moderator on the site
Awesome, it's easy and useful. Thaks
Reply
0
0
munish
about 8 years ago
#12677
This comment was minimized by the moderator on the site
easy and helpful in large working
Reply
0
0
Gaurav Pahuja
about 8 years ago
#13046
This comment was minimized by the moderator on the site
easy to use and helpful..:-p
Reply
0
0
Ajeet singh
about 8 years ago
#13306
This comment was minimized by the moderator on the site
Impotent work if duplicate value.
Reply
0
0
NAVEEN
about 7 years ago
#13445
This comment was minimized by the moderator on the site
hI In sheet1 we have three columns, 1st columns "fruits names" 2nd column Date of received, Name of supplies only to supplies and in 3rd 4th 5th columns are Normal defect, Major defects and Critical defects,all these in 1st sheet. in 2nd sheet, 3rd sheet, 4th sheet, saparetly post all of Normal defects in 2nd sheet, Major defects in 3rd sheet, critical defects in 4th sheet. when we are updating these sheet it automatically should update 1st individual in normal, Major, Critical. Thanks Naveen
Reply
0
0
NAVEEN
about 7 years ago
#13446
This comment was minimized by the moderator on the site
i had query regarding for eg: 1st sheet of work book 1st is column is with data received fruits, 2nd column is fruits names, 3rd column is for normal defects, 4th column is for Major Defects, 5th column is for Critical defects 2nd sheet for Normal Defects, 3rd sheet for Major Defects, 4th sheet for Critical Defects, My query is when we are updating these above sheet it total count should be reflected in 1st by individual fruits and for individual defects. Regards, Naveen kumar
Reply
0
0
Harrison
about 7 years ago
#13640
This comment was minimized by the moderator on the site
I am trying to label an individual data point as "1"...and if it has a duplicate, it will label the duplicates as "0"...but it would still label at least one of the data points as "1". Example, I could have one PO number on a truck, or multiple. Thanks
Reply
0
0
Amol
about 7 years ago
#13654
This comment was minimized by the moderator on the site
suppose there is a column which contains values as GR1, GR2, GR3 and so on..... but some also getting repeated again. how can i get the final count of the item. Like if it reaches to GR29, the the value should show as 29 in the formula cell
Reply
0
0
Brice
about 7 years ago
#13953
This comment was minimized by the moderator on the site
If you want to get a sum of duplicate values in a column(without counting the first one), try: =IF(COUNTIF($A$1:$A1,A1)-1>=1,1,0) For example, let's say that you have a same value 5 times. It will count 1 for each of the 4 duplicate values. Then, you just have to get a sum.
Reply
0
0
Fujilives
about 7 years ago
#14047
This comment was minimized by the moderator on the site
=COUNTIF($A$1:$A1,A1) This method for finding duplicates is amazing, because it allows you to do a simple filter on the column (just deselect 0 and 1) to show all 'duplicate entries' instead of 'entries that have duplicates'. What I mean by this, is I can then select ALL visible after the filter, and delete the entire rows, and be left with only a single entry of a row containing that item. For MANY projects, this is a fantastic way to filter things down quickly.
Reply
0
0
Ash
about 7 years ago
#14186
This comment was minimized by the moderator on the site
Let say I have different number of PO's in column A but some numbers are the same how can I count the total number of PO without including the duplicate number?
Reply
0
0
Rupesh Brahme Ash
about 5 years ago
#14187
This comment was minimized by the moderator on the site
use formula =SUMPRODUCT(1/COUNTIF(A1:A1483, A1:A1483&"")) =1/sumproduct(1/countif(range, criteria)) :-)
Reply
0
0
man Rupesh Brahme
about 2 months ago
#40419
This comment was minimized by the moderator on the site
Ty so much
Reply
0
0
mohammad
about 7 years ago
#14619
This comment was minimized by the moderator on the site
many many thanks :-)
Reply
0
0
Khan
about 6 years ago
#16067
This comment was minimized by the moderator on the site
Please help to resolve this issue site ID Supplier Line 12 abc good 12 VV good 12 TT good site ID Supplier Line 12 abc good Required Supplier Name - formula required to show "Multiple Suppliers" as against same site iD and line there are 3 different suppliers. Please help to resolve this issue.
Reply
0
0
Stefan
about 6 years ago
#16647
This comment was minimized by the moderator on the site
Thank you SO much for this post its exactly what I needed! Please could you tell me why your formula "=COUNTIF($A$2:$A2,A2)" didn't work as expected in my worksheet until I amended it to "=COUNTIF($A2:$A$2;A2)" ? i.e. switching the absolute references around Thanks in advance
Reply
0
0
Najam Ul Hassan
about 6 years ago
#16812
This comment was minimized by the moderator on the site
very nice formula for counting of duplicate. It is very helpful Thanks extend office team
Reply
0
0
Rhytha
about 6 years ago
#17025
This comment was minimized by the moderator on the site
I appreciate for the Solution provided. It is very helpful.
Reply
0
0
Unni
about 6 years ago
#17116
This comment was minimized by the moderator on the site
Hi, Please help me to solve the below problem =COUNTIFS('Weld Map'!$O$6:$O$7105,"="&F2,'Weld Map'!$V$6:$V$7105,"=Rej*",'Weld Map'!$AL$6:$AL$7105,"=REJ*") I need to count the text "REJ" from columns "V" and "AL" under the criteria of a period between F1 and F2