99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Avoid Duplication In Excel By Ensuring Unique Values
Sun 24th July 2011
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
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).
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
This was a fantastic course - Jens was hugely enthusiastic and we covered a huge amount of content. The pace was good - we were a little pushed for time as our session was condensed - but if we had more time I would have liked to do more practice exercises to test that I had understood everything.
The follow-up resources sound great, overall 10/10! :)
Excel Forecasting and Data Analysis
No comments, everything is good
Assistant GER Audit And Compliance Manager
Excel VBA Intermediate
Jens was great, really knows his stuff and his enthusiasm makes you want to learn!