Pulling Information from 1 Excel File to Another

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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 A1:D100 as an example.

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

Reply | Quote

It worked like a charm!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote