Sorting, Summing, and Gathering Data in Seperate Workbooks

[karinj]

HI everyone,

I am having some trouble with a work assignment and fairly new to excel and hoping someone will be able to assist. I will try my best to be as descriptive as possible.

i extracted some data from an outside source and it looked something like this:

WORKBOOK 1
[TABLE="width: 200"]
<tbody>[TR]
[TD]ID and Name
[/TD]
[TD]Product 1
[/TD]
[TD]Product 2
[/TD]
[TD]Product 3
[/TD]
[TD]Total
[/TD]
[TD]Max Limit
[/TD]
[/TR]
[TR]
[TD]1-abc
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]2-bcd
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-efg
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4-hij
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5-klm
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6-mno
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7-qrs
[/TD]
[TD]9
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]9
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8-tuv
[/TD]
[TD]12
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]12
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am responsible for obtaining the maximum limit column from another workbook with 4 separate tabs, each one representing a different zone, all containing the same information (but with different numbers, etc) similar to the one below:

WORKBOOK 2
[TABLE="width: 200"]
<tbody>[TR]
[TD]Client name
[/TD]
[TD]address
[/TD]
[TD]premium
[/TD]
[TD]broker name
[/TD]
[TD]broker ID
[/TD]
[TD]limit 1
[/TD]
[TD]limit 2
[/TD]
[TD]limit 3
[/TD]
[TD]sum
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]abd
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]def
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ghi
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]klm
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]nop
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]qrs
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]tuv
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]wxy
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Workbook 1 contains a broker name and ID number. workbook 2 has 4 tabs and I have to find the broker ID or name from one of those tabs. Once I locate which tab has the broker name, I have to find the sum of all the limits (1,2 and 3) and find the maximum limit for that broker. that information would then go into the max limit of workbook 1. when i am done doing this for all brokers on workbook 1, I obtain the highest number and that is my answer. There are about 400 distinct broker names and IDs on workbook 1.

I originally thought using a pivot table would be helpful, but then I would still have to create one pivot table for all 4 tabs and go through it 400 different times to tailor my results for each selection.

I also thought maybe combining all 4 tabs into one would help, and then possibly using the v lookup function? But not sure how to tackle that or how to combine the workbooks.

I hope this makes sense. ANy help would be greatly appreciated.

Thank you,
Karin

 

[Simon Lloyd]

Is the broker name in the first workbook always in the format "id-name" as in the second you have them split in two columns?

Regards,
Simon Lloyd
Microsoft Office Help

 

[karinj]

Yes, the first workbook contains both ID number and name in the same cell, whereas the second workbook separates them out. When I was searching in the second workbook's individual tabs, I was searching by the ID number only.

Is the broker name in the first workbook always in the format "id-name" as in the second you have them split in two columns?

Regards,
Simon Lloyd
Microsoft Office Help