Issue with rank formula

[richard1234567891011]

Hi guys, I need a rank formula, these are the data.

[/URL][/IMG]

The expected result is in the green column. So, rank the same values in one (multiple values all equal the same), without jumps of numbers, and consider the different names in another rank (Pescara and Chieti). I need a formula in order to do this.

Thank you guys.

 

[richard1234567891011]

This scenario - multiple cities with the same value and different ranks - and adding the possibility of blank cells, requires a complex array formula. Would this be convenient for you? Or would it not be more practical to eliminate blank rows?

Anyway, try this formula ...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B
​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

City​

[/TD]
[TD]

Value
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Rank​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

PESCARA​

[/TD]
[TD]

375
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

PESCARA​

[/TD]
[TD]

375​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

PESCARA​

[/TD]
[TD]

375​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

CHIETI​

[/TD]
[TD]

375​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

CHIETI​

[/TD]
[TD]

375​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

CHIETI​

[/TD]
[TD]

375
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

CHIETI​

[/TD]
[TD]

375
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

CHIETI​

[/TD]
[TD]

375​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

CHIETI​

[/TD]
[TD]

375​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

CHIETI​

[/TD]
[TD]

375​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

FARA IN SABINO​

[/TD]
[TD]

250
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

COSSATO​

[/TD]
[TD]

100
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

ALMENO S. SALVATORE​

[/TD]
[TD]

400
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

ALMENO S. SALVATORE​

[/TD]
[TD]

400
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in E2 copied down
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,SUM(IF(FREQUENCY(IF(A$2:A$100<>"",MATCH(A$2:A$100,A$2:A$100,0)),ROW(A$2:A$100)-ROW(A$2)+1),IF(B$2:B$100>B2,1)))+1+SUM(IF(FREQUENCY(IF(A$1:A1<>"",MATCH(A$1:A1,A$1:A1,0)),ROW(A$1:A1)-ROW(A$1)+1),IF(A$1:A1<>A2,IF(B$1:B1=B2,1)))),INDEX(E$1:E1,MATCH(A2,A$1:A1,0))))
confirmed with Ctrl+Shift+Enter

M.

There are no blank rows in the middle. The data will be added at the end. The issue with the formula before was related to blank cells at the end.
Anyway. This formula seems a real bomb and it was what I really hoping to find.
I actually have the original file in the working PC and I will able to check it in detail in September.
Anyway, It seems simply perfect!
Thank you very very much. You probably solved a really important thing for the excel that I am building.

Thank you Thank you!!!!!

 

[Marcelo Branco]

If the cities are grouped and there are no blank rows in the middle, maybe this simpler array formula


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

City​

[/td][td]

Value​

[/td][td][/td][td][/td][td]

Rank​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

PESCARA​

[/td][td]

375​

[/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

PESCARA​

[/td][td]

375​

[/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

PESCARA​

[/td][td]

375​

[/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

CHIETI​

[/td][td]

375​

[/td][td][/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

CHIETI​

[/td][td]

375​

[/td][td][/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

CHIETI​

[/td][td]

375​

[/td][td][/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

CHIETI​

[/td][td]

375​

[/td][td][/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

CHIETI​

[/td][td]

375​

[/td][td][/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

CHIETI​

[/td][td]

375​

[/td][td][/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

CHIETI​

[/td][td]

375​

[/td][td][/td][td][/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

FARA IN SABINO​

[/td][td]

250​

[/td][td][/td][td][/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

COSSATO​

[/td][td]

100​

[/td][td][/td][td][/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

ALMENO S. SALVATORE​

[/td][td]

400​

[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in E2 copied down
=IF(A2="","",SUM(IF(FREQUENCY(IF(A$2:A$100<>"",MATCH(A$2:A$100,A$2:A$100,0)),ROW(A$2:A$100)-ROW(A$2)+1),IF(B$2:B$100>B2,1)))+1+SUMPRODUCT(--(A$1:A1<>A2),--(B$1:B1=B2),--(MATCH(A$1:A1,A$1:A1,0)=ROW(A$1:A1)-ROW(A$1)+1)))
confirmed with Ctrl+Shift+Enter

M.