not allowing duplicate entries from a list

Hi folks,
I'm making up a spreadsheet for use at work using excel 2003 and I'm struggling a wee bit.

I have a bank of serial numbers which i've set up in column P I've used the validation List function on column B where the serial numbers will be entered, so that the user can only enter a number from the column P list. What I'm looking to do is block the user from entering a serial number in Column B which has allready been entered so that column B contains only one instance of the serial number. Can anyone point me in the right direction please?

Hi Kevska and welcome to the forum,

Will you accept some VBA code behind your workbook for an answer? I'm not seeing a good non-VBA method to do this. If your answer is "What's VBA", I'll have my answer.

You should send a sample workbook so the code works closer to what you have.

Click on "Go Advanced" then on the PaperClip Icon above the message area. This will allow you to attach a sample file. I'll write a little code after the party.

One straightforward (but less than ideal) option is to add another column Q and use the data in Q for your data validation list instead of P. Each row in Q would refer to the list in P, but remain blank if the value is already found in B.

So for example in Q2:

=IF(COUNTIF(B:B,P2)>0,"",P2)

The reason it is less than ideal is that your dropdown list will have blank lines in it, but it's a quick solution.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Let me work from the inside out.

COUNTIF(B:B,P2)

The Excel function COUNTIF counts the number of times a value occurs in a specified range. In this case it counts the number of times the value in P2 occurs in column B (B:B means use the whole column).

The IF part considers the result from that COUNTIF function.

If the count is more than 0, then we leave the cell blank, because that value is already being used.

Otherwise (i.e., the count is 0), we use the value as it appears in column P.