Thanks Sloth.

Your formula for finding the Top 3 largest revenue amounts per Cust ID works
fine.

However, finding the same Prod ID label associated w/ each of the Top 3
revenue amounts per Cust ID does not. It works in this simplified example,
but not in a dataset of 40,000+ rows (and I think you allude to this in your
reply).

Somehow, I think I need a formula that finds the row where the Top N revenue
amount and correct Cust ID occurs (using LARGE function from column C), and
then gets the corresponding Product ID related to the Row# that was captured.

Thanks again for your efforts,

"Sloth" wrote:

This is the closest I can get. The only restriction is the revenue must be
unique for that id number. I can't figure a way around it because the match
finds the first value in a list, and lookup functions need to be sorted.
With that being said here is my solution.

I used Book1 and Book2. It keeps the formulas from getting to big.
Customer ID's are numbers with a custom number format of
000000
ProductID's are text format as text

In C2 of Book1 use this formula to get the largest revenue for the ID number
=LARGE(([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),1)
In B2 of Book1 use this formula to get the matching product ID number for
the revenue
=IF(C2<0,INDIRECT("Sheet2!B"&1+MATCH(C2,([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),0)),"")
Both formulas are array formulas, which means you have to enter them by
pressing ctrl+shift+enter

Then, copy across but change E2 and G2 to look like this (respectively)
=LARGE(([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),2)
=LARGE(([Book2.xls]Sheet1!$A$2:$A$7=$A2)*([Book2.xls]Sheet1!$C$2:$C$7),3)

Then, copy down and you should get the aproppiate results. Hope this helps,
and sorry I couldn't get a perfect solution.

"Roddd" wrote:

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.