finding new and duplicate part numbers

**caligula7001** · 02-10-2010, 11:25 PM

=IF(ISNA(VLOOKUP(C1,$A$1:$A$31667,1,FALSE)),"NO","YES")

I have part numbers in both column A and Column C. I'm trying to find out which part numbers in column C are new or if they already exist in Column A

Above is my formula but it will only do one cell at a time. if I try and copy and paste, it says something about the size and it will only put a yes or no in one cell. Either I'm doing something wrong or there is a different formula that will put a yes or no in all cells in cloumn B

**DonkeyOte** · 02-11-2010, 06:06 AM

thread moved to Excel Worksheet Functions from Outlook Forum

**beaunydal** · 02-13-2010, 08:30 AM

If the Column C values being tested are in C1:C5000 then select B1 then click in the name box on the left of the Formula bar and type B5000. Hold down the Shift key while you press the Enter key. This selects B1:B5000. In the Formula bar type...
=IF(ISNA(VLOOKUP(C1:C5000,A1:A31667,1,0)),"NO","YES")
Then press Ctrl+Shift+Enter because it is an array formula.
Edit: On second thoughts this is probably not the solution. What exactly is the message received when you try to copy/paste?
Are there any merged cells getting in the way in column B?
Beau Nydal

**Palmetto** · 02-13-2010, 10:17 AM

Above is my formula but it will only do one cell at a time. if I try and copy and paste, it says something about the size and it will only put a yes or no in one cell.

There is nothing wrong with the formula. The problem is that your copy and paste range are not the same size as suggested by your comment.

To copy the formula down column-B, enter the formula into cell B1 then double-click the fill-handle on the cell pointer and Excel will auto-fill the formula down column-B for as many rows as there are data in either columns A or C.

Note: the "fill-handle" is the tiny square at the lower right of the cell pointer. The mouse pointer will change to a " + " shape when you hover over the fill handle.

Note2: if there are blanks in columns A or C, Excel will interpret this as then end of the date and NOT fill-down the remaining row. You may want to sort and put blanks at the bottom.