Results 1 to 8 of 8
  1. #1
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    205
    Thanks
    16
    Thanked 7 Times in 7 Posts

    Pulling Information from 1 Excel File to Another

    I will have the following Excel (2007) files:

    One file has part numbers and part descriptions (in rows).

    One file has part numbers and inventory counts, cost data, etc. (also in rows). (The routine that creates this file cannot also pull the part descriptions with the part numbers.)

    I want to be able to set up a means whereby for each part number, the formula or macro will then look in the first file for the same part number and copy the corresponding part description to a blank adjacent column in the second file.

    First file column headings would be part number and part description.

    Second file column headings would be part number and part description and then the rest of the column headings.

    I would likely have the macro/formula saved in its own file and paste the data from the second file into the workbook file with formula or macro and use it that way. I guess I could also add the macro/formula into the second file, too.

    Thanks.
    Ray
    OS: 2 computers w/ Win7 Home Premium 64-bit & 2 computer w/ Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    In the file that needs the part descriptions, try this formula: =VLOOKUP(A1,FIRST!$A$1:$B$8,2,FALSE)
    Where the part numbers are in the A column in the second file. Adjust the reference to A1 if the part numbers don't begin in row 1.

    And, where "FIRST" is the initial file and the part numbers are in the A column and the descriptions are in the B column.

  3. The Following User Says Thank You to kweaver For This Useful Post:

    WildcatRay (2016-08-23)

  4. #3
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    205
    Thanks
    16
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by kweaver View Post
    In the file that needs the part descriptions, try this formula: =VLOOKUP(A1,FIRST!$A$1:$B$8,2,FALSE)
    Where the part numbers are in the A column in the second file. Adjust the reference to A1 if the part numbers don't begin in row 1.

    And, where "FIRST" is the initial file and the part numbers are in the A column and the descriptions are in the B column.
    If I recall the VLOOKUP function correctly, the "2" is the column to draw the information from in the my FIRST file, correct?
    Ray
    OS: 2 computers w/ Win7 Home Premium 64-bit & 2 computer w/ Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Yes, the 2 in this case indicates you want the 2nd column from the range of columns A to B.
    If, for some reason, your part numbers were in the A column and you had other things in, say, columns B and C, and the description was in column D, you'd want the 4th column from the range A1100 as an example.

    VLOOKUP looks up things from the first column of the range and returns the column you indicated.

  6. The Following User Says Thank You to kweaver For This Useful Post:

    WildcatRay (2016-08-23)

  7. #5
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    205
    Thanks
    16
    Thanked 7 Times in 7 Posts
    It worked like a charm!
    Ray
    OS: 2 computers w/ Win7 Home Premium 64-bit & 2 computer w/ Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Several considerations using VLOOKUP:

    If the part numbers are numbers (ex, 100870 ) then the values being searched in the column index must also be numbers not text. Conversely, if the part number is text (ex, PN29389b) then the lookup value must also be text not numbers. If there is a mismatch, then a #N/A will be returned and special consideration in your formula must be taken to make sure the format category is the same

    VLOOKUP is not case sensitive so if you have two part numbers, PN0d54Rev and PN0D54rev, only the first part number will be matched.

    Lastly, if finding exact matches with VLOOKUP in a very large table, the formula can get bogged down.

    HTH,
    Maud

  9. #7
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    205
    Thanks
    16
    Thanked 7 Times in 7 Posts
    Part numbers are either only numbers or text (a mix of letters and numbers). Part descriptions are text (also a mix of letters and numbers). Everything worked fine.
    Ray
    OS: 2 computers w/ Win7 Home Premium 64-bit & 2 computer w/ Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  10. #8
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    205
    Thanks
    16
    Thanked 7 Times in 7 Posts
    formula: =VLOOKUP(A1,FIRST!$A$1:$B$8,2,FALSE)
    Are there any possible problems if the "$A$1:$B$8" is replaced by just the columns, "$A:$B"?

    formula: =VLOOKUP(A1,FIRST!$A:$B,2,FALSE)
    Last edited by WildcatRay; 2016-08-24 at 08:24.
    Ray
    OS: 2 computers w/ Win7 Home Premium 64-bit & 2 computer w/ Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •