Subtotaling and Ranking Items -- Help Needed!

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
Looking for a way to generate the 'Top X' table listed below using formulas (not autofilters) to filter through and subtotal/rank the data. As you can see, the user will select the region, quarter, and year, and the result should be a ranking of Top X Branches for the specified time period.

Thanks!
Book1.xls
ABCDEFGH
1BRANCHIDProduction$RegionDate
2134$82East4/1/07
3134$47East9/18/07ShowTop3BranchIDsfor:
4134$89East2/4/07
5134$54East8/15/07Region:East
6687$33East8/16/07Quarter:3
7555$44East4/5/07Year:2007
8687$64East8/15/07
9687$83East12/15/07TOP3
10898$81East7/17/07RankIDProduction
11898$87East3/11/071687$180
12789$87East2/2/072134$101
13789$11East3/20/073898$81
14546$54East1/22/07
15546$38East12/1/07
16138$45East6/11/07
17123$11North2/10/07
18123$88South1/8/07
19123$87South8/7/07
20123$57West9/8/07
21123$15North12/3/07
22700$211North12/19/07
23700$47North5/13/07
24794$65North4/18/07
DATA
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Pivot table approach...
Book4
BCDEFGHIJKL
1Production $RegionDateRegionEast
282East1-Apr-07
347East18-Sep-07Sum of Production $YearsDate
489East4-Feb-072007Grand Total
554East15-Aug-07BRANCH IDQtr1Qtr2Qtr3Qtr4
633East16-Aug-071348982101272
744East5-Apr-076879783180
864East15-Aug-078988781168
983East15-Dec-07Grand Total1768227983620
1081East17-Jul-07
1187East11-Mar-07
1287East2-Feb-07
1311East20-Mar-07
1454East22-Jan-07
1538East1-Dec-07
1645East11-Jun-07
1711North10-Feb-07
1888South8-Jan-07
1987South7-Aug-07
2057West8-Sep-07
2115North3-Dec-07
22211North19-Dec-07
2347North13-May-07
2465North18-Apr-07
25
DATA
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
Hmm... But I'd like the report to easily adjust to new data in the master table as it's pasted in. Won't the pivot table require the user to make edits to the formula/pivot table?

Basically I want someone other than me to be able to go in, add another quarter's worth of data to the existing sheet, and by selecting their region and quarter to report on, have the updated ranks appear. Don't want to rely on the user understanding pivot tables and my underlying formulas. Will your solution allow for such functionality?
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
Any other takers? I'll gladly email someone my sheet if they're willing to take a look and offer any input.

Thanks!
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127

ADVERTISEMENT

Can someone tell me if I can drive a pivot table using data selected via combo boxes, or data entered into certain cells? I essentially need pivot table output, but don't want my users needing to understand or having to work with the actual pivot table.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Can someone tell me if I can drive a pivot table using data selected via combo boxes, or data entered into certain cells? I essentially need pivot table output, but don't want my users needing to understand or having to work with the actual pivot table.

If you are on Excel 2003 or beyond, you can turn the data area into a list by means of Data|List|Create List. Users need only to activate the refresh button, make a selection from the PAGE field. Btw, invoking a formula system is also possible, but that wouldn't eliminate the choices the user has or wants to make.
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127

ADVERTISEMENT

work computers are on excel 2000, so i guess formulas are the way to go. Can you assist?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
work computers are on excel 2000, so i guess formulas are the way to go. Can you assist?
aaTop 3 FormulaApproach psulion01.xls
ABCDEFGHIJK
1BRANCH IDProduction $RegionDateBranch|Region|Quarter|YearRegionEastTop
213482East1-Apr-07134,East,2,2007Quarter333
313447East18-Sep-07134,East,3,2007Year2007
413489East4-Feb-07134,East,1,2007BRANCH IDSubTotalProduction $Branch
513454East15-Aug-07134,East,3,2007134101101134
668733East16-Aug-07687,East,3,20076879797687
755544East5-Apr-07555,East,2,2007555081898
868764East15-Aug-07687,East,3,200789881  
968783East15-Dec-07687,East,4,20077890
1089881East17-Jul-07898,East,3,20075460
1189887East11-Mar-07898,East,1,20071380
1278987East2-Feb-07789,East,1,20071230
1378911East20-Mar-07789,East,1,20077000
1454654East22-Jan-07546,East,1,20077940
1554638East1-Dec-07546,East,4,2007
1613845East11-Jun-07138,East,2,2007
1712311North10-Feb-07123,North,1,2007
1812388South8-Jan-07123,South,1,2007
1912387South7-Aug-07123,South,3,2007
2012357West8-Sep-07123,West,3,2007
2112315North3-Dec-07123,North,4,2007
22700211North19-Dec-07700,North,4,2007
2370047North13-May-07700,North,2,2007
2479465North18-Apr-07794,North,2,2007
DATA


E2, copied down:

=A2&","&C2&","&MATCH(MONTH(D2),{1,4,7,10},1)&","&YEAR(D2)

H5, copied down:

=SUMIF($E$2:$E$24,G5&","&$H$1&","&$H$2&","&$H$3,$B$2:$B$24)

K2:

=COUNTIF(H5:H14,">="&LARGE(H5:H14,J2))

J5, copied down:

=IF(ROWS($J$5:J5)<=$K$2,LARGE($H$5:$H$14,ROWS($J$5:J5)),"")

K5:

Control+shift+enter...

=IF(N(J5),INDEX($G$5:$G$14,SMALL(IF($H$5:$H$14=J5,ROW($H$5:$H$14)-ROW($H$5)+1),COUNTIF($J$5:J5,J5))),"")

and copy down.
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
thanks Aladin. Is there some way to accomplish this sort of thing without needing to copy the formulas in columns E and H? I'm trying to do this without modifying the master data sheet at all

I suppose I can run the calc on some other sheet in my workbook, but can you explain how I can automate the copy-down of the formulas. The # of rows in my dataset will constantly change, so I would need the macro to first figure out the size of the range and then copy the formulas down that number of rows.

Thanks again...very insightful.

mike
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,313
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top