Thanks for the help Max.

Unfortunately (and I should have mentioned this in the original post), Sheet
B contains roughly 10,000 Cust IDs and more than 1,000 product IDs, which is
beyond the columnar limit of an Excel pivot table.

Your example was very helpful.....it just does not solve my problem in this
case.

Thanks,
Rod

"Max" wrote:

Another play to try, which delivers exactly what's required ..

Let's simplify things a little by assuming
that sheet: A is within the same book as sheet: B

Sample construct available at:
http://www.savefile.com/files/9934508
LookUp_Top3_Prod_n_Rev_ByCust_Roddd_wks.xls

In sheet: B,
the source table below is assumed in cols A to C,
data from row2 down
[Cust ID in col A, Prod ID in col B, Revenue in col C]

Cust ID Prod ID Revenue
1 000013 ABC123 1,000
2 000011 ABC987 10,000
3 000014 ABC234 5,000
4 000014 ABC345 15,000
5 000011 ABC789 9,000
6 000014 ABC567 20,000

Create a pivot table (PT)
---------------------------
Select any cell within the source table
Click Data Pivot Table Report
Click Next Next
In step3 of the wiz.:
Drag and drop "Cust ID" within the ROW area
Drag and drop "Prod ID" within the COLUMN area
Drag and drop "Revenue" within the DATA area
(It'll appear as "Sum of Revenue")
Click Finish

The PT will be created in a new sheet just to the left of sheetB
Rename this sheet as: PT

In sheet: PT,
Using empty cols to the right of the PT, say cols AA, AB ...
Put in AA3: =IF(B3="","",B3-COLUMN()/10^10)
Copy across to AF3 to cover all the Prod IDs in row2,
then fill down to cover all the Cust ID lines in col A

In sheet: A,
Cust IDs are assumed listed in col A, data in A2 down

Cust ID
1 000011
2 000012
3 000013
4 000014
5 000015

Put in B2:
=IF(ISERROR(LARGE(OFFSET(PT!$AA$2:$AF$2,MATCH($A2, PT!$A:$A,0)-2,),1)),"",IND
EX(OFFSET(PT!$2:$2,,,,COUNTA(PT!$2:$2)-1),MATCH(LARGE(OFFSET(PT!$AA$2:$AF$2,
MATCH($A2,PT!$A:$A,0)-2,),1),OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,)
,0)+1))

Put in D2:
=IF(ISERROR(LARGE(OFFSET(PT!$AA$2:$AF$2,MATCH($A2, PT!$A:$A,0)-2,),2)),"",IND
EX(OFFSET(PT!$2:$2,,,,COUNTA(PT!$2:$2)-1),MATCH(LARGE(OFFSET(PT!$AA$2:$AF$2,
MATCH($A2,PT!$A:$A,0)-2,),2),OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,)
,0)+1))

Put in F2:
=IF(ISERROR(LARGE(OFFSET(PT!$AA$2:$AF$2,MATCH($A2, PT!$A:$A,0)-2,),3)),"",IND
EX(OFFSET(PT!$2:$2,,,,COUNTA(PT!$2:$2)-1),MATCH(LARGE(OFFSET(PT!$AA$2:$AF$2,
MATCH($A2,PT!$A:$A,0)-2,),3),OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,)
,0)+1))

B2, D2, F2 returns the Top 3 Prod IDs for the Cust ID in A2
The 3 formulas are identical, except for the numbers: 1,2,3 within the
LARGE(... , x), where x = 1,2,3

Put in C2:
=IF(ISERROR(LARGE(OFFSET(PT!$AA$2:$AF$2,MATCH($A2, PT!$A:$A,0)-2,),1)),"",IND
EX(OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,),MATCH(LARGE(OFFSET(PT!$AA
$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,),1),OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:
$A,0)-2,),0)))

Put in E2:
=IF(ISERROR(LARGE(OFFSET(PT!$AA$2:$AF$2,MATCH($A2, PT!$A:$A,0)-2,),2)),"",IND
EX(OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,),MATCH(LARGE(OFFSET(PT!$AA
$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,),2),OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:
$A,0)-2,),0)))

Put in G2:
=IF(ISERROR(LARGE(OFFSET(PT!$AA$2:$AF$2,MATCH($A2, PT!$A:$A,0)-2,),3)),"",IND
EX(OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,),MATCH(LARGE(OFFSET(PT!$AA
$2:$AF$2,MATCH($A2,PT!$A:$A,0)-2,),3),OFFSET(PT!$AA$2:$AF$2,MATCH($A2,PT!$A:
$A,0)-2,),0)))

C2, E2, G2 returns the corresponding revenue amounts for the Top 3 Prod IDs
for the Cust ID in A2. Here again, the 3 formulas are identical, except for
the numbers: 1,2,3 within the LARGE(... , x), where x = 1,2,3

Now just select B2:G2 and copy down as far as required

In the event of ties in the revenue amounts for the Top 3 Prod IDs for any
particular Cust ID, the tied Top 3 Prod IDs / amts would be displayed in the
same relative order of the 1st instances that these Prod IDs appear in
sheet: B.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Roddd" wrote in message
...
In workbook A, I have a unique list of customer IDs in column A:
Cust ID
1 000011
2 000012
3 000013
4 000014
5 000015

In workbook B, I have a bunch of product / revenue information related to
each customer. Each customer may have multiple rows of data on this
workbook, but each row would be a unique Cust & Prod ID combination:
Cust ID Prod ID Revenue
1 000013 ABC123 1,000
2 000011 ABC987 10,000
3 000014 ABC234 5,000
4 000014 ABC345 15,000
5 000011 ABC789 9,000
6 000014 ABC567 20,000

Back in Workbook A, I would like to populate columns B - G with the Top 3
revenue generating products and their associated revenue:

Cust ID TProd1 TRev1 TProd2 TRev2 TProd3 TRev3
1 000011 ABC987 10,000 ABC789 9,000
2 000012
3 000013 ABC123 1,000
4 000014 ABC567 20,000 ABC345 15,000 ABC234 5,000
5 000015

Any thoughts on getting this started? Thanks.