Creating new workbook based on data in another

[jRyan2003]

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...

 

[BrianB]

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]

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]

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]

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]

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]

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...


That should do the trick


Greetings monkey