Forum Discussion

itwa7853's avatar
itwa7853
Copper Contributor
Nov 05, 2019

Excel formula with barcode scanner

Hey everyone, I can't figure out how to create a formula to do the following - hopefully someone could help:

 

I have 2000 rows of 3 columns (Serial #s,  IMEI #s, and SIM card #s).On my spreadsheet, it needs to be listed in the order that the devices are physically boxed in, so I created a new spreadsheet and scanned all of barcodes of the device Serial #s to put them in the order they are all shelved in (physically in the warehouse).

 

Since I already have all of the raw data, I know it is possible to autofill the 2 other columns with the corresponding IMEI #s and SIM card #s for each Serial # (without going back and scanning the corresponding 2000 IMEI #s and 2000 SIM #s), but I don't know what formula to use to accomplish this.

excel
Formulas and Functions

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 05, 2019

    itwa7853 

    Assuming your new sheet has Serial# in column A and Location in column B, you can find the IMEI# and the SIM# from your first sheet (assume Serial in A, IMEI in B and SIM in C) via a VLOOKUP function. Easiest if you put both sheets in the same workbook. Sheet1 has your location data and Sheet2 your original list. I suppose that both sheets have a header row.

     

    The formula in C2 on Sheet1 could be =VLOOKUP(A2,Sheet2!A:C,2,FALSE) and in In D2 you put =VLOOKUP(A2,Sheet2!A:C,3,FALSE)

     

    Then you copy both formulae down your 2000 rows. Should work just fine for a one time solution. Good luck!

Resources