Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roddd
 
Posts: n/a
Default Looking up Top N using criteria

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default Looking up Top N using criteria

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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Looking up Top N using criteria

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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roddd
 
Posts: n/a
Default Looking up Top N using criteria

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.




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roddd
 
Posts: n/a
Default Looking up Top N using criteria

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.






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roddd
 
Posts: n/a
Default Looking up Top N using criteria

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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default Looking up Top N using criteria

Does it not work at all, or does it show the same Product ID for two equal
revenues?

I played around with some numbers and the highest value always works (in the
simplified example anyway), but the second and third ID's are the same as the
first if the revenues are equal. Is this what going on? Or is it always
wrong?

The problem is LARGE only finds the value not the position, and MATCH finds
the position of the first one only. There is a way around it though. If the
values are equal you can change the list to not include anything before the
first value. So you would MATCH the first value and use that number to start
the begining of the new list to MATCH. So you would have something like this.
=INDIRECT(~~MATCH(~~INDIRECT(~~MATCH)))
Give me some time to work on it.

"Roddd" wrote:

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.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Looking up Top N using criteria

Assumptions:

Source table...

A1:C1 contains Cust ID, Prod ID, and Revenue

A2:C7 contains your data

Results table...

F1:L1 contains Cust ID, TProd1, TRev1, etc.

F2:F6 contains 000011, 000012, 000013, 000014, and 000015

Formulas:

E2, copied down:

=IF(COUNTIF($A$2:$A$7,F2)3,SUM(IF(($A$2:$A$7=F2)* ($C$2:$C$7=LARGE(IF($A
$2:$A$7=F2,$C$2:$C$7),3)),1)),COUNTIF($A$2:$A$7,F2 ))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

G2:

=IF(INT((COLUMNS($G2:G2)-1)/2)+1<=$E2,INDEX($B$2:$B$7,MATCH(LARGE(IF($A$2
:$A$7=$F2,$C$2:$C$7-ROW($C$2:$C$7)/10^10),INT((COLUMNS($G2:G2)-1)/2)+1),$
C$2:$C$7-ROW($C$2:$C$7)/10^10,0)),"")

....confirmed with CONTROL+SHIFT+ENTER

H2:

=IF(INT((COLUMNS($G2:H2)-1)/2)+1<=$E2,INDEX($C$2:$C$7,MATCH(LARGE(IF($A$2
:$A$7=$F2,$C$2:$C$7-ROW($C$2:$C$7)/10^10),INT((COLUMNS($G2:H2)-1)/2)+1),$
C$2:$C$7-ROW($C$2:$C$7)/10^10,0)),"")

....confirmed with CONTROL+SHIFT+ENTER

Select/highlight G2:H2. Then copy across and down or place cursor over
the lower right corner of H2, click, drag across, and drag down.

Hope this helps!



In article ,
"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.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Looking up Top N using criteria

I forgot to mention that this solution will take into consideration any
ties for third place. In other words, if there are two or more products
tied for third place, all will be displayed.

In article ,
Domenic wrote:

Assumptions:

Source table...

A1:C1 contains Cust ID, Prod ID, and Revenue

A2:C7 contains your data

Results table...

F1:L1 contains Cust ID, TProd1, TRev1, etc.

F2:F6 contains 000011, 000012, 000013, 000014, and 000015

Formulas:

E2, copied down:

=IF(COUNTIF($A$2:$A$7,F2)3,SUM(IF(($A$2:$A$7=F2)* ($C$2:$C$7=LARGE(IF($A
$2:$A$7=F2,$C$2:$C$7),3)),1)),COUNTIF($A$2:$A$7,F2 ))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

G2:

=IF(INT((COLUMNS($G2:G2)-1)/2)+1<=$E2,INDEX($B$2:$B$7,MATCH(LARGE(IF($A$2
:$A$7=$F2,$C$2:$C$7-ROW($C$2:$C$7)/10^10),INT((COLUMNS($G2:G2)-1)/2)+1),$
C$2:$C$7-ROW($C$2:$C$7)/10^10,0)),"")

...confirmed with CONTROL+SHIFT+ENTER

H2:

=IF(INT((COLUMNS($G2:H2)-1)/2)+1<=$E2,INDEX($C$2:$C$7,MATCH(LARGE(IF($A$2
:$A$7=$F2,$C$2:$C$7-ROW($C$2:$C$7)/10^10),INT((COLUMNS($G2:H2)-1)/2)+1),$
C$2:$C$7-ROW($C$2:$C$7)/10^10,0)),"")

...confirmed with CONTROL+SHIFT+ENTER

Select/highlight G2:H2. Then copy across and down or place cursor over
the lower right corner of H2, click, drag across, and drag down.

Hope this helps!

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roddd
 
Posts: n/a
Default Looking up Top N using criteria

Domenic....thanks a great deal. This logic works perfectly.

"Domenic" wrote:

Assumptions:

Source table...

A1:C1 contains Cust ID, Prod ID, and Revenue

A2:C7 contains your data

Results table...

F1:L1 contains Cust ID, TProd1, TRev1, etc.

F2:F6 contains 000011, 000012, 000013, 000014, and 000015

Formulas:

E2, copied down:

=IF(COUNTIF($A$2:$A$7,F2)3,SUM(IF(($A$2:$A$7=F2)* ($C$2:$C$7=LARGE(IF($A
$2:$A$7=F2,$C$2:$C$7),3)),1)),COUNTIF($A$2:$A$7,F2 ))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

G2:

=IF(INT((COLUMNS($G2:G2)-1)/2)+1<=$E2,INDEX($B$2:$B$7,MATCH(LARGE(IF($A$2
:$A$7=$F2,$C$2:$C$7-ROW($C$2:$C$7)/10^10),INT((COLUMNS($G2:G2)-1)/2)+1),$
C$2:$C$7-ROW($C$2:$C$7)/10^10,0)),"")

....confirmed with CONTROL+SHIFT+ENTER

H2:

=IF(INT((COLUMNS($G2:H2)-1)/2)+1<=$E2,INDEX($C$2:$C$7,MATCH(LARGE(IF($A$2
:$A$7=$F2,$C$2:$C$7-ROW($C$2:$C$7)/10^10),INT((COLUMNS($G2:H2)-1)/2)+1),$
C$2:$C$7-ROW($C$2:$C$7)/10^10,0)),"")

....confirmed with CONTROL+SHIFT+ENTER

Select/highlight G2:H2. Then copy across and down or place cursor over
the lower right corner of H2, click, drag across, and drag down.

Hope this helps!



In article ,
"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.




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Looking up Top N using criteria

You're welcome, Roddd !
Glad to see you've got a solution from Domenic ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Roddd" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning Results Based on Two Criteria [email protected] Excel Worksheet Functions 7 October 23rd 05 02:53 PM
Find largest alphanumeric value matching alpha criteria in databas Alison Excel Worksheet Functions 7 August 4th 05 06:59 PM
sorting more than 3 keys Brooke Excel Discussion (Misc queries) 3 June 18th 05 04:52 AM
Countif using format criteria....not number criteria? Troy Excel Worksheet Functions 1 April 20th 05 04:50 AM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 02:37 AM


All times are GMT +1. The time now is 08:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"