Creating new workbook based on data in another

jRyan2003

Board Regular
Joined
Aug 28, 2003
Messages
83
OS=W2k
XL=2k

I'm having some trouble trying to figure this out. I have 2 workbooks. One has a large amount of data (approx 4500 rows). The other has names and id numbers. What I need to do is look at each name in the 2nd workbook and retrieve all the rows from the first workbook that match the id numbers associated with the name in the 2nd workbook and put it on a new sheet with that persons name on it.


Example:

Workbook1.xls has the following:
.......A........*........B.......*........C.......
5-5-03.........JS1518.........Phoenix
7-6-03.........JT1243.........Tucson
3-17-02.......KM1809........Dallas
10-23-02.....JT1243.........Los Angeles
9-9-03........JS1518.........Denver

Workbook2.xls has the following:
.......A.......*.........B.......
Tom............JT1243.......
Fred............JS1518.......
Tom............KM1809......


The macro/code should look at workbook2 and take JT1243 and find every occurance of it in Workbook1/column B and take each occurance and place it in a worksheet named Tom. By this example, Tom would have everything for JT1243 and KM1809. It should go through the entire list in Workbook2 until all of the entries have been copied from workbook1.

Hope this makes sense.... Any thoughts or tips on how to do this?

Thanks...
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
I would do it round the other way.

Make a new column in sheet 1 with a VLOOKUP() to get the name and sort on this.
 

jRyan2003

Board Regular
Joined
Aug 28, 2003
Messages
83
BrianB said:
I would do it round the other way.

Make a new column in sheet 1 with a VLOOKUP() to get the name and sort on this.

Thanks for the quick response... I've used Vlookup before, but I can't see how it would apply here. I would think this would call for some sort of custom macro to do what I have described.
 

Monkey

Active Member
Joined
Feb 10, 2003
Messages
251
Try the formula index(TotalRangeWorkbook2, match(B1, ColumnBWorbook2,0),1)

In other words if you comparing data in the second row then your formula would be

index(Sheet2!$A$3:$B$10000, match(B2, Sheet2!$B$1:$B$10000,0),1)

I Hope this works out for you

Greetings monkey
 

jRyan2003

Board Regular
Joined
Aug 28, 2003
Messages
83

ADVERTISEMENT

BrianB said:
I would do it round the other way.

Make a new column in sheet 1 with a VLOOKUP() to get the name and sort on this.

Sometimes I'm like a lightbulb that's not screwed in all the way. All I need is a little tweak and the light will shine brightly again.

I figured out what you meant by using VLOOKUP. I was able to get this to work fine using the following:

=VLOOKUP(D2,[Test.xls]Sheet1!$A$2:$B$246,2,FALSE)
Then filldown to the bottom of the spreadsheet.

However, when I try to automate this along with all the other steps to create the workbook, I'm getting hung up when it trys to copy and paste the VLOOKUP formula into the spreadsheet. This is where it gets hung:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(D2,[Test.xls]Sheet1!$A$2:$B$246,2,FALSE)"

When it stops, D2 is replaced in the formula with a REF! error. Is it possible to paste a formula like this? Or is there an easier way to insert this formula into the cell?

Thanks for the help...
 

Monkey

Active Member
Joined
Feb 10, 2003
Messages
251
You are using Formular1c1 that means your formula should look like:
Try Activecell.formula or even better:
PLace the formula in your worksheet.
Goto Record Macro
Select the Cell which contains the macro and press F2 followed by Enter. Stop recording.

Replace

"=VLOOKUP(D2,[Test.xls]Sheet1!$A$2:$B$246,2,FALSE)"

with the new formula recorded just a minute ago


That should do the trick


Greetings monkey
 

jRyan2003

Board Regular
Joined
Aug 28, 2003
Messages
83
Monkey said:
You are using Formular1c1 that means your formula should look like:
Try Activecell.formula or even better:
PLace the formula in your worksheet.
Goto Record Macro
Select the Cell which contains the macro and press F2 followed by Enter. Stop recording.

Replace

"=VLOOKUP(D2,[Test.xls]Sheet1!$A$2:$B$246,2,FALSE)"

with the new formula recorded just a minute ago

Recording a new macro didn't work for me, but going with the Activecell.formula did...

Thanks for the help... (y)


That should do the trick


Greetings monkey
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,895
Messages
5,766,978
Members
425,392
Latest member
Booknerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top