Ranking with duplicates

[michael.collins22]

I have an array of data, produced by formulas, that ranks NFL teams and over/under calls.

K L M N
BUF 7 Over1 25
DET 8 Over2 25
BAL 11 Under3 25
CLE 15 Over4 24
CHI 2 Over5 9
JAX 11 Over6 29
SEA 1 Over7 15
ARI 30 Over8 31
CAR 21 Over9 4
TB 21 Over10 25
SF 6 Over11 13
HOU 3 Over12 18
CIN 10 Over13 18
SD 18 Under14 21
PHI 5 Under15 14
STL 17 Under16 31

I want to keep the array, but add a vertical list next to it with the rankings reshuffled while including duplicates to look something like this.

Q R
1 SEA
2 CHI
3 HOU
4 Over9
5 PHI
6 SF
7 BUF
8 DET
9 Over5
10 CIN
11 BAL
11 JAX
13 Over11
14 Under15
15 CLE
15 Over7
17 STL
18 SD
18 Over12
28 Over13
21 CAR
21 TB
21 Under14
24 Over4
25 Over1
25 Over2
25 Under3
25 Over10
29 Over6
30 ARI
31 Over8
31 Under16

Currently, I have to produce the vertical list manually. I came up with a formula that will look up and rank the teams and calls, but it ignores duplicates. I type in numbers in column Q and have the following formula in column R. I'd rather have a formula(s) that will lookup and rank the teams and calls including duplicates. Any suggestions?

=IF(ISNUMBER(MATCH($Q1,$L$1:$L$16,0)),HLOOKUP($K$1,$K$1:$K$16,MATCH($Q1,$L$1:$L$16,0)),IF(ISNUMBER(MATCH($Q1,$N$1:$N$16,0)),HLOOKUP($M$1,$M$1:$M$16,MATCH($Q1,$N$1:$N$16,0)),""))

 

[jfish1288]

you can run this code

Sub gamblingman()
lastrow = Range("K" & Rows.Count).End(xlUp).Row
startrow = 2
Columns("Q:R").EntireColumn.Delete
Range("K" & startrow, "K" & lastrow).Copy Destination:=Range("R" & startrow)
Range("L" & startrow, "L" & lastrow).Copy Destination:=Range("Q" & startrow)
Range("M" & startrow, "M" & lastrow).Copy Destination:=Range("R65536").End(xlUp).Offset(1, 0)
Range("N" & startrow, "N" & lastrow).Copy Destination:=Range("Q65536").End(xlUp).Offset(1, 0)
Range("Q" & startrow).End(xlDown).Sort Key1:=Range("Q" & startrow), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

I wasnt sure what row your info started on. I assumed it was row 2, but you can change that on the third line

 

[michael.collins22]

Thanks for the response. I am a bit lost though. How do I run the Code? I am not sure what that means?

 

[jfish1288]

When you open your workbook, click on the sheet that has this info then click Alt + F11. On the left of the window that pops up double click the correct sheet or "ThisWorkbook". A new window box will pop up to the right. Copy and paste the code I wrote into this box. Click run (it looks like a "play" button, a little sideways triangle) and you should be good to go.

 

[michael.collins22]

Is your code meant for a Macro and are your instructions for a Windows OS? I have Excel for Mac 2008 so they weren't following with what I was seeing on the screen.

If your code is for a Macro, I will figure out how to run it on my end. Thank you again for your help.

 

[jfish1288]

yes to both of your questions. sorry i do not know how to do it on a mac

 

[michael.collins22]

Like I said, thank you for helping me out. I will work on setting up the Macro. Is there any function or formula that can accomplish the same thing though?

 

[jfish1288]

I think that would be difficult to do because of the duplicates and I would imagine that you will not have 16 rows each week, bye weeks and playoffs. If you dont want to use a macro I would just copy past and sort.

 

[michael.collins22]

Thank you again for your help.

 

[michael.collins22]

I wanted to give a shout out to Rebuild8 for coming up with this formula to solve the problem...

Hi,
If you didn't get the macro to run, you can try the formula below. Paste it in Q1, then press CTRL+SHIFT+ENTER to enter the formula as array formula. Copy down to row 32. Use the same formula in R1, except change both INDEX function arrays: L$1:L$16 to K$1:K$16, and N$1:N$16 to M$1:M$16 (and don't forget to CTRL+SHIFT+ENTER)

=IFERROR(INDEX(L$1:L$16,MATCH(SMALL(IF(ISNUMBER(L$1:N$16),L$1:N$16+ROW($1:$16)/10000+COLUMN(L:N)/100),ROW()),L$1:L$16+ROW($1:$16)/10000+COLUMN(L1)/100,0)),INDEX(N$1:N$16,MATCH(SMALL(IF(ISNUMBER(L$1:N$16),L$1:N$16+ROW($1:$16)/10000+COLUMN(L:N)/100),ROW()),N$1:N$16+ROW($1:$16)/10000+COLUMN(N1)/100,0)))