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.

 

[Marcelo Branco]

Maybe...

E2 copied down
=SUMPRODUCT(--(A$2:A$14<>A2),--(B$2:B$14>B2),--(MATCH(A$2:A$14,A$2:A$14,0)=ROW(A$2:A$14)-ROW(A$2)+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))

M.

 

[richard1234567891011]

Maybe...

E2 copied down
=SUMPRODUCT(--(A$2:A$14<>A2),--(B$2:B$14>B2),--(MATCH(A$2:A$14,A$2:A$14,0)=ROW(A$2:A$14)-ROW(A$2)+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))

M.

The result is #N/A. The formula, unfortunately is not working.

 

[Peter_SSs]

Is there any reason that 375|PESCARA gets a rank of 2 and 375|CHIETI gets a rank of 3? Could it just as well be that 375|PESCARA gets a rank of 3 and 375|CHIETI gets a rank of 2?

 

[richard1234567891011]

Is there any reason that 375|PESCARA gets a rank of 2 and 375|CHIETI gets a rank of 3? Could it just as well be that 375|PESCARA gets a rank of 3 and 375|CHIETI gets a rank of 2?

The only reason is that every name need a different rank (without repeating the same name and without jumps ranks) It is irrelevant if before there is Chieti o Pescara.

 

[Peter_SSs]

The only reason is that every name need a different rank (without repeating the same name and without jumps ranks) It is irrelevant if before there is Chieti o Pescara.

OK, thanks.
I can't think of an easy way with standard worksheet formulas, but you could try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RankSpecial(lValue As Long, sCity As String, rData As Range) As Long
  Static SL As Object
  Dim a As Variant
  Dim i As Long
  Dim s As String
  
  If SL Is Nothing Then Set SL = CreateObject("System.Collections.Sortedlist")
  a = rData.Value
  SL.Clear
  For i = 1 To UBound(a)
    s = 10 ^ 10 - a(i, 2) & "|" & a(i, 1)
    If Not SL.ContainsKey(s) Then SL.Add s, a(i, 2)
  Next i
  RankSpecial = SL.IndexOfKey(10 ^ 10 - lValue & "|" & sCity) + 1
End Function

Excel Workbook
	A	B	C
1	City	Value	Rank
2	PESCARA	375	3
3	PESCARA	375	3
4	PESCARA	375	3
5	CHIETI	375	2
6	CHIETI	375	2
7	CHIETI	375	2
8	CHIETI	375	2
9	CHIETI	375	2
10	CHIETI	375	2
11	CHIETI	375	2
12	FARA IN SABINA	250	4
13	COSSATO	100	5
14	ALMENNO S.SALVATORE	400	1
Rank

 

[Phuoc]

Try this in C2:

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,
SUM(IFERROR(1/COUNTIFS($A$2:$A$14,$A$2:$A$14,$B$2:$B$14,">"&B2),0))+SUM(IFERROR(1/COUNTIFS($A$2:A2,$A$2:A2,$B$2:B2,B2),0)),
VLOOKUP(A2,$A$1:C1,3,0))

Array formula, Enter with Ctrl+Shift+Enter.

 

[Marcelo Branco]

The result is #N/A. The formula, unfortunately is not working.

Result #N/A???? Don't understand how a formula using SUMPRODUCT can generate such result , unless there are errors or blank rows in your data. Check.

Worked perfectly for me...


[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]

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]
</tbody>[/TABLE]


Formula in E2 copied down
=SUMPRODUCT(--(A$2:A$14<>A2),--(B$2:B$14>B2),--(MATCH(A$2:A$14,A$2:A$14,0)=ROW(A$2:A$14)-ROW(A$2)+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))

M.

 

[richard1234567891011]

Thank you guys,
Sorry if I answer now, I was some days in holiday.
Both formula made by Marcelo Branco and Phuoc are indeed working.
The only issue in both is that are not able to read blank cells. Do you think that is it possible to adjust the one formula in order to make it to read also blank cells? The issue is that my colleagues are not very skilled in excel and they will need to add data. New data will not be read in with both formulas.
Regarding the macro made by Peter_SSs, I am sure that it is correct, but the issue is the same, my colleagues are not skilled in excel, I don't want for this reason a macro inside.

Thank you very much guys.

 

[Marcelo Branco]

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.