98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
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
=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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsSotheby's
Travelling Exhibitions Coordinator Jasmine Rudder Loagn Multiple applications Dont change anything, Andrew was fab! The Abbeyfield Society
PA TO FINANCE DIRECTOR AND HR DIRECTOR Excel Introduction I came here with no knowledge of Excel and I feel so much more confident now after a day of training. Imperial College
Space Auditor Harminder Bharj Excel Pivot Tables Excellent Course and trainer! |
PUBLICATION GUIDELINES