Hi fellow Excel Addict,
Thanks for joining me today for another 'Excel in Minutes' tip that I hope you will be able to use. If you missed Tuseday's newsletter that showed how to 'Change the Starting Page Number for Your Footer', you can read it here.
I am often asked about how to remove duplicates from a list but maybe you might want to consider preventing duplicates from being entered in the first place. In today's tip, I will show you how you can set up a range of cells to prevent duplicates from being entered. I hope you'll find this tip helpful.
Please feel free to share it with anyone you think could use a little Excel help.
Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Prevent Duplicate Values in a Column
If you've ever needed a way to prevent users from entering duplicate values in a specific range of cells, you can do this using Excel's Data Validation feature.
Let's use an example where we want to prevent users from entering duplicate values in cells B5:B50.
1) Select cells B5:B50. In most cases, with duplicate entries, you will be dealing with a single column, however, this can be used on any range of cells;
2) From the Data tab click Data Validation;
3) Click the Settings tab;
4) Click the drop down arrow under Allow and select Custom. A new Formula box appears;
5) In the Formula box, enter this formula...
Explanation of the formula: The COUNTIF part of this formula looks in the range B5 to B50 and counts the number of cells that are equal to the value in cell B5. The =1 part of the formula says that any value in this range may occur only once. Therefore 1=1 equals TRUE and the entry is allowed. When you try to enter a duplicate value, the result of the COUNTIF part of the formula is 2. The 2=1 equals FALSE and the error is detected.
Note that the formula uses absolute references for the range you want to apply the Data Validation criteria to ($B$5:$B$50) and relative referencing for the input cell you want to evaluate (B5). This is critical for this to work.
Absolute Referencing uses $ symbols to lock the column/row references so they do not adjust when a formula is moved or copied. Relative Referencing doesn't use $ symbols, therefore when a formula is move or copied, the references adjust based on the relative position of the rows and columns. So, in this example, when this data validation rule is applied, the data validation formula for cell B6 will be =COUNTIF($B$5:$B$50,B6)=1 . Notice that only the 'Relative' reference to cell B5 adjusted to B6 because it is one row down, however the range B5:B50 did not adjust in the formula because it used Absolute Referencing.
6) On the Input Message tab you can create a message here that will pop up whenever the user selects any cell within the validation range (i.e. B5:B50). An input message is great for situations where you have a data validation rule applied to an single cell but, for a range of cells, that message would be popping up every time you selected a cell. Annoying! So, in this case, you can leave the input message blank;
7) On the Error Alert tab you can create a message that will appear if an entry typed into a cell breaks the data validation rule. You can enter a Title if you want but the Title box is limited to only 32 characters. So, if you want to use a longer message, you can leave the Title blank and enter your message in the 'Error message' box (i.e. 'Duplicate values are not permitted in this column !!');
8) Click OK and try entering a duplicate value to test it.
Caution: Excel's Data Validation feature is not foolproof. This feature works only on direct user input. Duplicate values resulting from (a) data being pasted in to the range, (b) Find & Replace operations, (c) the result of calculations or (d) changes made by macros (VBA) code will override this feature.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630