Avoid Duplication In Excel By Ensuring Unique Values

Sun 24th July 2011

If you are typing random values into a column in an Excel worksheet, it is virtually impossible to keep track of which values you have already entered, and so duplication is a real possibility. With a simple formula, however, excel can install a sentry at the gate to stop any duplicates getting in. This article shows how to ensure unique values.

Billy has just bought a job lot of hundreds of ex-rental films on DVD with the intention of starting up a DVD rental business of his own. His first, rather laborious task is to log all of the titles in an Excel spreadsheet. As the DVDs are ex-rental, they already have numbered stickers on them and so Billy decides to log the titles using these existing numbers. This makes sense, as it will save him the trouble of renumbering the entire stock and putting new stickers onto each disc, with a corresponding one on each box.

Before he starts, he sees a potential snag in his plan. As the DVDs came from more than one bankrupt rental store, there could be some duplication of numbers. It would be difficult to keep track of which numbers have and haven't been used when logging hundreds of randomly numbered titles. Having two titles with the same number could cause untold confusion, so Billy must come up with a way to ensure there is no duplication, or he'll have to start from scratch.

He gets around this simply by typing in all of the titles with their respective numbers, duplicates and all. When the list is complete, Billy selects the column containing the numbers and then clicks on Data and then Sort. In the dialog box he clicks the Ascending radio button and then OK. This sorts the data numerically and any duplicates will be paired next to each other. Billy simply has to run his finger down the column to weed them out.

This was a smart piece of improvisation by Billy, but he could easily miss a duplicated number, so it is not fool-proof. It would be far better if there was a way to prevent duplicated numbers entering the list at all.

You will probably not be surprised to learn that Excel has a way of doing just that. With the creation of a simple formula Billy can instruct Excel to ensure unique values. This means that it becomes impossible to enter a value into a cell if that value already exists in the column.

You could try this for yourself quite easily by entering a few random numbers into column B and following these steps.

Select the cells containing the values you have typed, and the click on Data and choose Validation from the menu. Click the arrow on the right of the Allow: box and select Custom from the list. In the formula box enter the following

=MATCH(B1,$B:$B,0)=ROW(B1)

Click OK

Note: This formula relates to cells in my example of column B. If you want to ensure unique values in other columns, simply replace each letter B in the above formula with the letter representing the column you wish to modify.

Now if you try to enter a number that already exists in that column you will get the following message:

The value you entered is not valid.

A user has restricted values that can be entered into this cell.

This has solved Billy's problem ideally. He enters the data, safe in the knowledge that he has a sentry on guard to stop infiltration by duplicates. Each time a duplicate number crops up, Billy simply puts that DVD to one side to be renumbered later.
At the end of it all Billy is left with only half a dozen duplicated numbers. Renumbering these six titles will be a far easier job than applying new stickers to his entire stock.

I should point out, however, that this formula will only work with values that have been entered above. If you want insert unique values into columns that contain data above and below, you should use this formula (again this is for column B).

=COUNTIF($B:$B,B1)<2

Billy discovered that yet again Excel has a facility that will perform a certain task automatically. You can find out just how many such tasks Excel can perform, by enrolling on a training programme and learning how to get the most out of this amazing application.

Author is a freelance copywriter. For more information on excel training in london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1824-avoid-duplication-in-excel-by-ensuring-unique-values.html