Excel 2007, forbidding repeats within a column?

If you only have one column storing phone numbers, then using Data Validation with a Custom formula will prevent duplicates in that column:

=COUNTIF( B:B, B1 ) < 2

But if you have phone numbers being entered in several columns and rows all over a worksheet, then you will need to create some VBA code that will work whenever data changes anywhere on the worksheet.

It would need to take place on the Worksheet_SelectionChange event and search all cells to see if there is a match somewhere. It would be a small loop to do the search, and it would run fairly fast.

Columns:16,384 Rows:10,48,576

---EDIT---

When you select a range of cell to add Data validation too, the B! in this case represents the first cell in the data range. You are wanting to count how many times B1 occurs in a data range. If B1 is the first cell in the selected range you are applying the validation to, you would use B1 in the formula. Excel will then automatically adjust the B1 in the formula to B2, for cell B2, B3 for cell B3.

So if you select A1 to Z1000 for data validation, you would use...

=COUNTIF($A$1:$Z$1000, A1)<2

...because A1 is the first cell in the selected range.

I hope that helps.

---

You want to use the Data Validation feature. The example below prevents duplicate entries in B1 to B100

1) Select the range of cells that you wish to validate. (In this example: B1:B100)

2) Click Data Tools group > Data Tab > Data Validation.

3) Click the Settings tab in the Data Validation dialog box

4) Select "Custom" in the Allow drop-down list

5) Put this formula in the Formula input box

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

6) Specify Excel’s response to the invalid data on the Error Alert tab.

OK

The formula counts all the values in the range B1:B100 that equal the value in B1. If there is more than 1, it gives the user an Error Alert message.

The B1 in the formula is a relative reference and will change to B2, B3, etc. for the other cells in column B.

.