Search a text into another worksheet and if found, copy the entire raw in the table

%3CLINGO-SUB%20id%3D%22lingo-sub-921617%22%20slang%3D%22en-US%22%3ESearch%20a%20text%20into%20another%20worksheet%20and%20if%20found%2C%20copy%20the%20entire%20raw%20in%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-921617%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20i%20am%20trying%20to%20find%20a%20way%20to%20automatically%20populate%20a%20table%20with%20some%20data%20from%20another%20worksheet.%3C%2FP%3E%3CP%3EFor%20example%2C%20i%20have%20an%20worksheet%20with%20raw%20data%20like%20the%20one%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F138681i7CB153BDB13B21E7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eand%20another%20worksheet%20with%20the%20table%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F138682i55DF3619AB8A186E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20would%20like%20to%20create%20a%20VBA%20code%20or%20something%20to%20help%20me%20automatically%20import%20data%20from%20the%20raw%20data%20worksheet%20to%20the%20table%20from%20the%20other%20worksheet.%3C%2FP%3E%3CP%3EThe%20primary%20worksheet%20is%20the%20one%20with%20the%20table%20and%20based%20on%20the%20serial%20number%20I%20introduce%20in%20the%20A%20column%20to%20import%20the%20data%20to%20column%20B%20from%20the%20worksheet%20with%20raw%20data.%3C%2FP%3E%3CP%3ECan%20anyone%20please%20assist%20or%20guide%20me%3F%20Much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-921617%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-926082%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20a%20text%20into%20another%20worksheet%20and%20if%20found%2C%20copy%20the%20entire%20raw%20in%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-926082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F429990%22%20target%3D%22_blank%22%3E%40Alincristi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20the%20attached%20workbook%20(an%20example%20with%20som%20random%20part%20numbers)%20contains%20%26nbsp%3Bthe%20answer%20to%20your%20question.%20No%20need%20for%20VBA.%20A%20simple%20VLOOKUP%20will%20do.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-926788%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20a%20text%20into%20another%20worksheet%20and%20if%20found%2C%20copy%20the%20entire%20raw%20in%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-926788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bthank%20you%20for%20the%20answer.%20It%20works%2C%20but%20in%20my%20situation%20i%20have%20the%20raw%20data%20into%20another%20worksheet%20where%20I%20regularly%20update%20with%20new%20information.%20Will%20vlookup%20work%20with%20different%20worksheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-932867%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20a%20text%20into%20another%20worksheet%20and%20if%20found%2C%20copy%20the%20entire%20raw%20in%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-932867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F429990%22%20target%3D%22_blank%22%3E%40Alincristi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20it%20will%20work%20across%20workbooks.%20Say%20your%20raw%20data%20sits%20in%20%22Book1%22.%20Make%20sure%20your%20formula%20looks%20like%20this%3A%26nbsp%3B%3DVLOOKUP(%24A%242%2C%5BBook1%5DRawData!A%3AG%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20make%20the%20formula%20easier%20to%20maintain%20(if%20applicable)%20when%20you%20create%20a%20named%20range%20for%20your%20table%20that%20contains%20the%20raw%20data.%20So%2C%20if%20you%20would%20name%20the%20range%20of%20columns%20A%20to%20G%20in%20sheet%20%22RawData%22%20to%20e.g.%20%22DataList%22%20the%20formula%20would%20become%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24A%242%2CBook1!DataList%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20find%20such%20formulae%20easier%20to%20read.%20But%20that's%20just%20my%20personal%20opinion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello all, i am trying to find a way to automatically populate a table with some data from another worksheet.

For example, i have an worksheet with raw data like the one below.

clipboard_image_0.png

and another worksheet with the table below.

clipboard_image_1.png

I would like to create a VBA code or something to help me automatically import data from the raw data worksheet to the table from the other worksheet.

The primary worksheet is the one with the table and based on the serial number I introduce in the A column to import the data to column B from the worksheet with raw data.

Can anyone please assist or guide me? Much appreciated.

3 Replies
Highlighted

@Alincristi 

Perhaps the attached workbook (an example with som random part numbers) contains  the answer to your question. No need for VBA. A simple VLOOKUP will do. 

Highlighted

@Riny_van_Eekelen thank you for the answer. It works, but in my situation i have the raw data into another worksheet where I regularly update with new information. Will vlookup work with different worksheet?

Highlighted

@Alincristi 

Yes it will work across workbooks. Say your raw data sits in "Book1". Make sure your formula looks like this: =VLOOKUP($A$2,[Book1]RawData!A:G,2,FALSE)

 

You can make the formula easier to maintain (if applicable) when you create a named range for your table that contains the raw data. So, if you would name the range of columns A to G in sheet "RawData" to e.g. "DataList" the formula would become:

 

=VLOOKUP($A$2,Book1!DataList,2,FALSE)

 

I also find such formulae easier to read. But that's just my personal opinion.