SOLVED

Checking Serial Number, VBA or Function help

%3CLINGO-SUB%20id%3D%22lingo-sub-2322968%22%20slang%3D%22en-US%22%3EChecking%20Serial%20Number%2C%20VBA%20or%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322968%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20that%20contains%20the%20issued%20serial%20numbers%20for%20components.%20Column%20A%20has%20the%20serial%20number%20and%20Column%20B%20has%20the%20part%20number.%20I%20want%20to%20have%20a%20verification%20in%20column%20C%20if%20the%20serial%20number%20has%20been%20used%20already%20for%20the%20part%20number%20being%20entered%20in%20Column%20B.%20My%20best%20guess%20was%20to%20use%20a%20If%2FAND%20function%20to%20check%20if%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(B2%3AB100%3DB2%2CA2%3AA100%3DA2)%2C%22Used%22%2C%22Open%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20idea%20is%20that%20this%20will%20check%20column%20B%20if%20it%20matches%20B2%20which%20is%20ASD01-1%20and%20then%20check%20column%20A%20if%20it%20matches%20A2%20which%20is%201.%20If%20both%20match%20the%20statement%20%22Used%22%20will%20be%20displayed.%20However%20the%20formula%20does%20not%20work.%20Any%20help%20on%20the%20matter%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2322968%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2323177%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20Serial%20Number%2C%20VBA%20or%20Function%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2323177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044959%22%20target%3D%22_blank%22%3E%40Mullikee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(COUNTIFS(%24B%242%3A%24B%24100%2CB2%2C%24A%242%3A%24A%24100%2CA2)%26gt%3B1%2C%22Used%22%2C%22Open%22)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a workbook that contains the issued serial numbers for components. Column A has the serial number and Column B has the part number. I want to have a verification in column C if the serial number has been used already for the part number being entered in Column B. My best guess was to use a If/AND function to check if:

 

=IF(AND(B2:B100=B2,A2:A100=A2),"Used","Open")

 

My idea is that this will check column B if it matches B2 which is ASD01-1 and then check column A if it matches A2 which is 1. If both match the statement "Used" will be displayed. However the formula does not work. Any help on the matter would be appreciated.

1 Reply
best response confirmed by Mullikee (New Contributor)
Solution

@Mullikee 

=IF(COUNTIFS($B$2:$B$100,B2,$A$2:$A$100,A2)>1,"Used","Open")

Fill down.