Rank Uniquely by criteria and value...

[RossShep92]

Hi

I am trying to rank values in descending order using a criteria (in this case major customer group) and value, but want the ranks to also be unique.

I have used some sumproduct formulas that seem to get me close, but then i cannot ensure that the rank value is unique.

A sample of the data is below with an idea of how i need to rank the data. Excel sheet is a few thousand rows, and i will be ranking multiple numerical columns based on the "customer" and "values" within the columns so i cant sort.

Help appreciated!

Thanks,
Ross

Customer	Value	Rank
Discounters	13​	4​
Discounters	4​	5​
Discounters	16​	3​
Discounters	16​	3​
Discounters	16​	1​
Others	1​	4​
Others	3​	3​
Others	7​	1​
Others	5​	2​
Export	20​	9​
Export	20​	8​
Export	40​	7​
Export	40​	6​
Export	40​	5​
Export	40​	4​
Export	60​	3​
Export	60​	2​
Export	60​	1​
ASDA	8​	1​
ASDA	2​	2​
ASDA	1​	3​
ASDA	1​	4​

 

[J.Ty.]

Hi,

Welcome to the Board!

I have written a ranking formula for you. Certainly, it takes position of the entries into account, too.

Book1
	A	B	C	D
1	Customer	Value	Rank	New rank
2	Discounters	13	4	5
3	Discounters	4	5	9
4	Discounters	16	3	6
5	Discounters	16	3	7
6	Discounters	16	1	8
7	Others	1	4	19
8	Others	3	3	20
9	Others	7	1	22
10	Others	5	2	21
11	Export	20	9	10
12	Export	20	8	11
13	Export	40	7	12
14	Export	40	6	13
15	Export	40	5	14
16	Export	40	4	15
17	Export	60	3	16
18	Export	60	2	17
19	Export	60	1	18
20	ASDA	8	1	4
21	ASDA	2	2	3
22	ASDA	1	3	1
23	ASDA	1	4	2
Sheet1

Cell Formulas
Range	Formula
D2:D23	D2	=COUNTIF($A$2:$A$23,"<"&A2)+COUNTIFS($A$2:$A$23,A2,$B$2:$B$23,"<"&B2)+COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)

 

[DanteAmor]

Hi and welcome to MrExcel!

Check if this you need:

Book1
	A	B	C
1	Customer	Value	Rank
2	Discounters	13	4
3	Discounters	4	5
4	Discounters	16	1
5	Discounters	16	2
6	Discounters	16	3
7	Others	1	4
8	Others	3	3
9	Others	7	1
10	Others	5	2
11	Export	20	8
12	Export	20	9
13	Export	40	4
14	Export	40	5
15	Export	40	6
16	Export	40	7
17	Export	60	1
18	Export	60	2
19	Export	60	3
20	ASDA	8	1
21	ASDA	2	2
22	ASDA	1	3
23	ASDA	1	4
sheet

Cell Formulas
Range	Formula
C2:C23	C2	=COUNTIFS($A$2:$A$23,A2,$B$2:$B$23,">"&B2)+COUNTIFS(A$2:A2,A2,B$2:B2,B2)

 

[RossShep92]

Thank you very much for the welcome.

I think this is exactly what i need

I will give it a whirl tomorrow at work and let you know the outcome.

Thanks again,
Ross

 

[RossShep92]

Hi,

Welcome to the Board!

I have written a ranking formula for you. Certainly, it takes position of the entries into account, too.

Book1
	A	B	C	D
1	Customer	Value	Rank	New rank
2	Discounters	13	4	5
3	Discounters	4	5	9
4	Discounters	16	3	6
5	Discounters	16	3	7
6	Discounters	16	1	8
7	Others	1	4	19
8	Others	3	3	20
9	Others	7	1	22
10	Others	5	2	21
11	Export	20	9	10
12	Export	20	8	11
13	Export	40	7	12
14	Export	40	6	13
15	Export	40	5	14
16	Export	40	4	15
17	Export	60	3	16
18	Export	60	2	17
19	Export	60	1	18
20	ASDA	8	1	4
21	ASDA	2	2	3
22	ASDA	1	3	1
23	ASDA	1	4	2
Sheet1

Cell Formulas
Range	Formula
D2:D23	D2	=COUNTIF($A$2:$A$23,"<"&A2)+COUNTIFS($A$2:$A$23,A2,$B$2:$B$23,"<"&B2)+COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)

Hi

Thank you for the welcome but i dont think its quite what i needed.

I needed individual rankings by "customer" so each customer was ranked from high to low values.

I believe the reply below has done this

Thanks,
Ross

 

[RossShep92]

Hi and welcome to MrExcel!

Check if this you need:

Book1
	A	B	C
1	Customer	Value	Rank
2	Discounters	13	4
3	Discounters	4	5
4	Discounters	16	1
5	Discounters	16	2
6	Discounters	16	3
7	Others	1	4
8	Others	3	3
9	Others	7	1
10	Others	5	2
11	Export	20	8
12	Export	20	9
13	Export	40	4
14	Export	40	5
15	Export	40	6
16	Export	40	7
17	Export	60	1
18	Export	60	2
19	Export	60	3
20	ASDA	8	1
21	ASDA	2	2
22	ASDA	1	3
23	ASDA	1	4
sheet

Cell Formulas
Range	Formula
C2:C23	C2	=COUNTIFS($A$2:$A$23,A2,$B$2:$B$23,">"&B2)+COUNTIFS(A$2:A2,A2,B$2:B2,B2)

Thank you very much for the welcome.

I think this is exactly what i need

I will give it a whirl tomorrow at work and let you know the outcome.

Thanks again,
Ross

 

[DanteAmor]

I think this is exactly what i need

I'm glad to help you. Thanks for the feedback.