Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |