Match and insert corresponding data across spreadsheets

[patrickt]

Hello, I have a task that would take me hours to complete and I'm sure there's got to be a way to do it in Excel 2007. I need to get part numbers from one master cross reference spreadsheet to individual spreadsheets. The master cross reference spreadsheet has our company 7-digit number AND the manufacturer part number, while the individual spreadsheets only have the manufacturer part number. I need to have the individual spreadsheets match manufacturer part numbers and then insert our company's 7-digit number line-by-line.

Master spreadsheet (snippet):

7-digit PN MFG PN
2206422 A04-04L
2209624 A04-04S
2203436 A04-054
2208761 A04-06A
2208897 A04-06C
2208942 A04-06D
2209292 A04-06E
2209108 A04-06F
2209627 A04-06S
2208764 A04-08A
2208900 A04-08C

Individual spreadsheets (snippet):

7-digit PN MFG PN

? A04-604
? A04-605
? A04-606
? A06-604
? A06-605
? A06-606
? A06-608
? A06-610
? A08-606
? A08-608
? A08-610

Hopefully this makes some sense.

Thanks!

[JBeaucaire]

Forum examples sometimes lose something in the display. If you posted a sample workbook showing your need, before/after examples, I'm sure it will be clearest and result in the fastest solution.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[patrickt]

Thanks for the tip. If you open the "Individual-spreadsheet.xlsx", you can see the first column is blank and in need of a 7-digit number from the "Master-cross-reference.xls". I have many of these "individual spreadsheets" and only one master. For example, the first row in column "7-digit" in the "Individual-spreadsheet.xlsx" should be 2203720 (if you search the master for "A04-604" it matches up to 7-digit number 2203720. Basically, I need a routine I can use to open each "individual spreadsheet" and reference the "Master-cross-reference.xls" to insert a 7-digit number in the first column of the individual spreadsheet depending on the "MFG PN" in the second column (for every row in the individual spreadsheet)...

[JBeaucaire]

Don't really need a macro for something like this. In A3, enter this formula, copy down, then convert the results to values with COPY>EDIT>PASTESPECIAL - Values on that column.

=INDEX('[Master-cross-reference.xls]Sheet1'!$A:$A, MATCH(B4,'[Master-cross-reference.xls]Sheet1'!$B:$B, 0))

[patrickt]

Thank you. You are awesome! Very much appreciated!

[patrickt]

At first it wasn't quite matching correctly. I changed the formula you gave to MATCH(B3 instead of MATCH(B4...

=INDEX('[Master-cross-reference.xls]Sheet1'!$A:$A, MATCH(B3,'[Master-cross-reference.xls]Sheet1'!$B:$B, 0))

Thanks. Works perfectly...

[JBeaucaire]

Of course, my bad. Nice catch!