In an Excel spreadsheet, how can I search for a value within the same column?

Question

I have a column named 'barcode' where each item possesses a unique barcode. So, I am generating an Excel template for the user, displaying all current items along with their name, barcode, and other fields using Node.js with the xlsx-populate library from the backend. I aim to implement validation for the barcode column, so that if a user attempts to add a new item with an existing barcode, an error message will be returned. Additionally, if the user attempts to edit a barcode for one of those items, an error should also be returned if the new barcode already exists.

So, I am looking for a formula in column barcode, within the range B2:B9999, to validate each input and search within the same column to check if the barcode already exists.

I tried this but it didn't work at all. Also, I faced a problem where I tried to edit the barcode (2500012780009) but it changes into 2.50001E+13.

sheet.range('B2:B99999').dataValidation({
    type: 'CUSTOM',
    formula1: `=IF(COUNTIF($B$2:B2, B2)>1, "Barcode already exists", TRUE)`,
    showDropDown: true,
    showErrorMessage: true,
    errorTitle: 'Invalid Barcode',
    error: 'Please select a valid barcode.',
    errorStyle: 'stop',
    errorAlert: 'warning',
  });

I would appreciate any help.

Answer 1

You can use data validation on the column to check for duplicates on entry or change.

First thing is to prevent the long numbers from being displayed as an exponential notation:

1. Select all of Column B and change the cell format to Text

2. With column B still selected, add Data Validation

   • Select the Data tab on the ribbon
   • Click on Data Validation, then Data Validation from the drop down menu
   • Select Custom in the Allow drop down menu
   • in the formula text box enter the following: =COUNTIF(B:B,B1)=1
   • Click on the Error Alert Tab and define for error message

Validation should now prevent duplicate values