Home

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
Alincristi
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

@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. 

@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?

@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.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies