Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't know how.

Say, I'm entering data anyplace within column B, and I want to know if the inputting value is already elsewhere in the column (above or below the current cell) and don't allow if there is a duplicate.

Edit:

  1. I've seen answers like this one on this site, but they all have an range say from B1 to B20, how do I have this validation on the entire column, is this possible?

  2. Seem like when I drag on the cell, duplicated values can appear, it is only when I try to input the value that this validation works. So, how do I restrict on the dragging as well as manual input on this?

share|improve this question

4 Answers 4

Instructions for avoiding duplicates for Excel 2000 are here.

share|improve this answer
    
Thank you for your answer. And, please see my Edit. –  ray247 Jul 18 '10 at 3:43

Also, just a side note.... when referring to particular cells in a column (like in your example) you would refer to them as B1:B20. To refer to the whole column, you can use B:B. That describes an array of all data in the B column. Hope that helps.

share|improve this answer
    
Thank you so much, this answered part of my question, upvoted! –  ray247 Jul 19 '10 at 17:43

You can only deal with copying over validation with VBA:

http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/

share|improve this answer

I answer only your question 2. You have to write your own Private Sub Worksheet_Change routine.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.