Excel Extract/Merge. I have 2 Excel docs.?

Question

Doc 1 is my master part # list, and doc 2 has just certain part #s.

Doc 1 has a "leadtime" column in it that I need to carry over to doc 2, but ONLY for the part#'s that are on doc 2.

Doc 2 has about 1400 part numbers on it, doc 1 (the master doc) has about 200,000.

Is there a way to do this in Excel?

Answer 1

Yes there is a way to do this, but you'll have to split the master document up into about 4 spreadsheets. There's only one good way of doing this if you don't want to spend years matching these together and that is with a macro. The problem is that macros take a descent little bit of expertise to develop. So it's time for me to give you a crash course in macros. First you need to make sure that your security level in Excel for macros is set to Medium or Low. From the menu bar, go to Tools, Macro, Security. Reset it to Medium save the file, then restart Excel. Excel needs to be restarted for the change to take effect. Now on to creating macros. Go to Tools, Macro, Record New Macro. Pick a name for your macro then click OK. Don't do anything whatsoever to the Excel workbook, just click the stop button. This creates basically a blank macro named Macro1 or whatever you called it in your workbook. Now we want to go edit that macro. Go to Tools, Macro, Macros.

Now select your macro and click Edit. Now past the following text in as macro code:

'Set column A to your part number and column B to your lead time
For Each x In [Doc2! A:A] 'Looks through every cell in column A in a worksheet called Doc2
If x.FormulaR1C1 = "" Then 'Tells computer to end the search if it reaches
Exit For 'a blank cell in column A of Doc2
End If

For Each y In [Doc1! A:A] 'Looks through every cell in column A in a worksheet called Doc1
If y.FormulaR1C1 = "" Then 'Tells computer to go to the next part number in Doc2 if it reaches
Exit For 'a blank cell in column A of Doc1
End If

If x.FormulaR1C1 = y.FormulaR1C1 Then 'Compares the part numbers to see if they match
x.Offset(0, 1).Value = y.Offset(0, 1).Value 'Sets column B in Doc2 equal to column B in Doc1 (your lead times)
Exit For 'Moves to next part number in Doc2
End If
Next y 'Moves to next part number in Doc1
Next x 'Moves to next part number in Doc2


Everything after an apostrophe is comments and will appear green. This will have no affect on how the macro runs. You will need to modify the macro or you worksheets so the macro works properly. The code gets pasted between the Sub Macroname and End Sub portions. The comments I listed should help you understand how the macro works. Here's something REALLY IMPORTANT!!! Save your workbook BEFORE you run the macro. You can't undo what a macro does by hitting the undo button. So if it doesn't do what you want, simply close the file without saving it.

Contact me, I probably need to explain a few more things too you. Since your master list is over one spreadsheet long there are going to be some complications you'll have to work through that could make it very difficult, even for a macro.

Macros can save hours or days of time, but they can be difficult to a new user. The Visual Basic 6 Blackbook by Coriolis is a good reference guide if you want to learn to write macros in Excel. Just remember the coding may be a little different in Excel than for writing a program in VB6.

Answer 2

No, you can't do that in Excel. Single Excel worksheet handles 65536 rows of data. Your master is too long and won't fit in one worksheet. Your best bet is to use Microsoft Access. Import or link the two docs to Access as data tables. Then use the query wizard to figure out the lead time for the parts in doc2. I'm assuming you know how to use Access. Otherwise, you'd need to learn that first, which is quite easy to learn.

If your master doc has fewer than 65536 rows, then you can use the vlookup function to populate the lead time.

Answer 3

=if(a2>2,a million,0) will attempt cellular a2, and if more advantageous than 2 will positioned a a million in the outcomes cellular . If no longer >2, will positioned a nil..the a million and nil will be even if you wish them to be. miss the 0 and the formulation will go away the reply sparkling. Use the formulation help in Excel to assist with those issues.

Answer 4

you need to do a vlookup. go to the help index on how to.