Forum Discussion

saeedom1988's avatar
saeedom1988
Copper Contributor
Jul 27, 2019
Solved

return the data from another table

Hi  I'm trying to get the data from the table in the next sheet  so when I make counting for the inventory I make sure that it,s the correct item  I just need when I reed the barcode with the barc...
excel
saeedom1988's avatar
saeedom1988
Copper Contributor
Jul 28, 2019

Hi tauqeeracma thank you for your help 

this can't work because it works with only 1 column 

I have 3 columns of barcodes and eath one of them have his special Unit in a separate column  

so when I take a read of barcode I want it to know from which column and then return the unit next to that column 

                 column 1   /  column 2   /   column 3 /   column 4   /  column 5  /   column 6

row 1            barcode 3  /  unit3        /    barcode 2 /  unit 2        /   barcode 1 /   uint1 

row 2           63555262 / Psc             /    63555263 /  dozen       /    63555264 / carton 

 

so if I take a read and if it was 63555262 then it returns in the UOM column in the stock count sheet 

Psc and if I reed the 62555263 then return dozen and so on 

I hope that I clear what I need and I thank you all in advance 

 my regards 

  • saeedom1988's avatar
    saeedom1988
    Copper Contributor
    Jul 28, 2019

    Hi tauqeeracma 

    thanks a lot Tauqeer that what I need I hope all the best for you and if you don't mind to advice me in the formula and the speed of the file

    are there a way to adjust the formula so when there are more than 5400 rows it will update automatically 

    "=IF([@[Product Barcode]]="","",(IFERROR(VLOOKUP([@[Product Barcode]],list!$B$4:$C$5400,2,0),IFERROR(VLOOKUP([@[Product Barcode]],list!$D$4:$E$5400,2,0),IFERROR(VLOOKUP([@[Product Barcode]],list!$F$4:$G$5400,2,0),"")))))"

    and does the 3 Vlookup in the same formula needs a lot of memory because I intend to use this file in tablet I'm afraid it will get slow when I'm counting 

    is there a way to make this file lighter and work fast in a tablet in general

    thanks from the heart  

    best regards 

    Saeed 

    • tauqeeracma's avatar
      tauqeeracma
      Iron Contributor
      Jul 28, 2019
      You are most welcome.

      In case number of rows are more than 5,400 then you just need to edit the range in the formula. For example if you have 7,000 rows then your new formula will be :

      IF([@[Product Barcode]]="","",(IFERROR(VLOOKUP([@[Product Barcode]],list!$B$4:$C$7000,2,0),IFERROR(VLOOKUP([@[Product Barcode]],list!$D$4:$E$7000,2,0),IFERROR(VLOOKUP([@[Product Barcode]],list!$F$4:$G$7000,2,0),"")))))"

      I think due to some extra formatting your file is slow. I will try to simplify it and update you accordingly. Three vlookup() in a same formula will not consume more memory.

      Thanks
      Tauqeer
      • saeedom1988's avatar
        saeedom1988
        Copper Contributor
        Jul 30, 2019

        Hi tauqeeracma 

         

        these are the final file

        if you can check to find a faster way to work with me it will be helpful 

         

        I will use the tablet for counting the product in the store 

        then I will copy the data and paste it in the second file to find the difference and make the inventory report 

        thanks for all the help you give and I wish all the best for you 

        my regards 

        Saeed

Resources