Difficult Ranking Problem

[easybpw]

Hi everyone,

Thanks in advance for the help. I am trying to rank a column based on specific criteria. I have it working up to a point. See below. As you can see I am ranking A, B, C, etc. based on the column that starts with 23.91. The column to the right of the B works fine, it is ranking everything based on the letter. =IF($O3=" "," ",SUMPRODUCT((O3=$O$3:$O$2000)*(J3<$J$3:$J$2000))+1). Column O is the letters, and J is 23.91. I then tried to rank just by the letters and don't want to include the dollar amounts for any other letter but this formula =IF($O3="A",RANK.EQ(P3,$P$3:$P$703,1)," ") is including them. As you can see below 22.55 should be ranked 2 and 22.33 should be and so on but it's not. What can I do to get it the way I need?

 

[Fluff]

Can you post some sample, to save us from having to re-create it.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[easybpw]

Hi and thank you for the response. Due to work restrictions I had a hard time with your request but was able to create a dummy file that has the same scenario and problem. As you can see I want to rank each sales category by itself and I did accomplish this but the ranking is taking into consideration all sales.

Sales Analysis.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date of Sale	Type	Market Share	Category	Sale Rank	Outside Sale Rank	Internal Sale Rank	Donations	CoWorker Sales	Misc. Sales
2	7/28/2022	Outside	22.50	A	2	6
3	7/29/2022	Internal	22.22	B	1		1
4	7/20/2022	Internal	18.20	B	4		15
5	7/21/2022	Outside	26.00	A	1	1
6	8/1/2022	Coworker	21.25	D	1				1
7	8/2/2022	Misc.	3.21	E	3					11
8	8/2/2022	Donation	5.00	C	3			11
9	8/4/2022	Coworker	7.00	D	2				6
10	8/5/2022	Misc.	4.20	E	2					6
11	8/6/2022	Internal	18.22	B	3		11
12	8/7/2022	Internal	19.64	B	2		6
13	8/8/2022	Outside	22.10	A	4	15
14	8/9/2022	Donation	8.20	C	1			1
15	8/10/2022	Outside	22.20	A	3	11
16	8/11/2022	Donation	7.20	C	2			6
17	8/12/2022	Misc.	6.00	E	1					1
18	8/12/2022	Outside	19.90	A	5	17
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=IF($B2="Outside","A",IF($B2="Internal","B",IF($B2="Donation","C",IF($B2="Coworker","D",IF($B2="Misc.","E")))))
E2:E18	E2	=IF($D2=" "," ",SUMPRODUCT((D2=$D$2:$D$18)*(C2<$C$2:$C$18))+1)
F2	F2	=IF($D2="A",RANK.EQ($E2,$E$2:$E$18,1)," ")
G2:G18	G2	=IF($D2="B",RANK.EQ($E2,$E$2:$E$18,1)," ")
H2:H18	H2	=IF($D2="C",RANK.EQ($E2,$E$2:$E$18,1)," ")
I2:I18	I2	=IF($D2="D",RANK.EQ($E2,$E$2:$E$18,1)," ")
J2:J18	J2	=IF($D2="E",RANK.EQ($E2,$E$2:$E$18,1)," ")
F3:F18	F3	=IF($D3="A",RANK.EQ(E3,$E$2:$E$18,1)," ")

 

[Fluff]

Thanks for that.
How about for F2

=IF($D2="A",SUMPRODUCT(($D2=$D$2:$D$18)*($E2>$E$2:$E$18))+1,"")

 

[Fluff]

Or maybe just

=IF($D2="A",$E2,"")

 

[easybpw]

Thanks for the help. This almost works like I need it to. And honestly, the 2nd formula you provided is the easiest, which sometimes is the less obvious. My only issue is if column B has the same number for the same category. I don't want the same rank. So for example if outside sales had 2 days at 25% and that was the top number I don't want 2 ranks of 1. The first would be 1 and then the 2nd would be 2 etc.

 

[Fluff]

So for example if outside sales had 2 days at 25% and that was the top number I don't want 2 ranks of 1.

But that's what the formula in col E will give you.

 

[easybpw]

Yes, true. But there are other outside workbooks and formulas on different sheets that columns F, G, H, & I are included in and I can't mix categories together. If I strictly use column E then I can't separate out the categories. Hope that makes sense.

 

[Fluff]

But you originally said that you wanted to ignore the prices & use the rank.

 

[easybpw]

I am getting myself a bit confused. I apologize. You all are the experts here. Ultimately what I need is to rank each "Type" column B by the Market Share, column C. While I did that in column D, Category, I need to have the rank for each type in it's own column on the spreadsheet. If I need to change some of how I have it set up I can do that. But I thought using the category column would make it easier to accomplish what I need.