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

Resources