Rank 5 lowest bids and name the carrier

Baseball Guy

New Member
Joined
Apr 9, 2009
Messages
3
I have a spreadsheet with over 2,100 trucking lanes (rows) and 90 plus carriers (columns). Each carrier bids on a lane and rate is entered in the corresponding cell. I would like to rank each lane with the lowest 5 bids in order and name the carrier. Is there a formula(s) to accomplish this?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with over 2,100 trucking lanes (rows) and 90 plus carriers (columns). Each carrier bids on a lane and rate is entered in the corresponding cell. I would like to rank each lane with the lowest 5 bids in order and name the carrier. Is there a formula(s) to accomplish this?
Do you mean name the carriers?

For results, are you just looking for the name(s) or are you looking for the bid and name? If the latter, can you explain where/how you want the results presented?

What should happen if, say, a situation like this occurred? Two bids are equal lowest and then four equal bids are next lowest and then three equal bids are next lowest ...
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I am giving a sample sheet 1 and a macro
the result is in sheet 2 which is also given below
the macro is dynamic and perhaps will work in your database
Keep your original file carefully some where and then experiment with this macro;. Do cosmetic changes like headings etc in sheet 2
post feedback

the macro is
Code:
Sub test()
Dim rng As Range, x(), y()
Dim j As Integer, k As Integer, cfind As Range
Dim m As Integer, n As Integer
'j = 0
'k = 0

With Worksheets("sheet1")
m = .Range("a1").End(xlDown).Row
n = .Range("a2").End(xlToRight).Column
'MsgBox "m-1=  " & m - 2 & "  n-2=" & "  " & n - 2
ReDim x(1 To m - 1, 1 To n - 1)
ReDim y(1 To m - 1, 1 To n - 1)
For j = 0 To 3
Set rng = Range(.Range("B2").Offset(j, k), .Range("B2").Offset(j, k).End(xlToRight))
'MsgBox rng.Address

For k = 0 To 4
x(j + 1, k + 1) = WorksheetFunction.Small(rng, k + 1)
Set cfind = rng.Cells.Find(what:=x(j + 1, k + 1), lookat:=xlWhole)
'MsgBox cfind.Address
y(j + 1, k + 1) = .Cells(1, cfind.Column)

'MsgBox x(j + 1, k + 1)
'MsgBox y(j + 1, k + 1)
Next
k = 0
Next
End With

With Worksheets("sheet2")



For j = 0 To 3
If j >= 4 Then GoTo line1
.Range("A2").Offset(j, 0) = Worksheets("sheet1").Range("a2").Offset(j, 0)
For k = 0 To 4

'MsgBox x(j + 1, k + 1)

'MsgBox .Range("B2").Offset(j, k * 2).Address
.Range("B2").Offset(j, k * 2) = x(j + 1, k + 1)


'MsgBox y(j + 1, k + 1)
'MsgBox .Range("B2").Offset(j, k * 2).Offset(0, 1).Address
.Range("B2").Offset(j, k * 2).Offset(0, 1) = y(j + 1, k + 1)
Next
Next
End With
line1:
End Sub
Baseball Guy 2.xls
ABCDEF
1carrierstruckinglaneqwert
2a683807576
3s4857224243
4d8227173463
5f745771588
Sheet1
Baseball Guy 2.xls
ABCDEFGHIJK
1
2a3w68q75r76t80e
3s22e42r43t48q57w
4d17e27w34r63t82q
5f7e15r57w74q88t
Sheet2
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

try this revised macro and post feedback

Code:
Sub test()
Dim rng As Range, x(), y()
Dim j As Integer, k As Integer, cfind As Range
Dim m As Integer, n As Integer
'j = 0
'k = 0

With Worksheets("sheet1")
m = .Range("a1").End(xlDown).Row
n = .Range("a2").End(xlToRight).Column
'msgbox "m-1=  " & m - 1 & "  n-2=" & "  " & n - 1
ReDim x(1 To m - 1, 1 To 5)
ReDim y(1 To m - 1, 1 To 5)
For j = 0 To m
Set rng = Range(.Range("B2").Offset(j, k), .Range("B2").Offset(j, k).End(xlToRight))
'msgbox rng.Address

For k = 0 To 4
If j = m - 1 Then GoTo line2
x(j + 1, k + 1) = WorksheetFunction.Small(rng, k + 1)
Set cfind = rng.Cells.Find(what:=x(j + 1, k + 1), lookat:=xlWhole)
'msgbox cfind.Address
y(j + 1, k + 1) = .Cells(1, cfind.Column)

'msgbox x(j + 1, k + 1)
'msgbox y(j + 1, k + 1)
Next
k = 0
Next
End With
line2:
With Worksheets("sheet2")



For j = 0 To m - 1
If j >= m - 1 Then GoTo line1
.Range("A2").Offset(j, 0) = Worksheets("sheet1").Range("a2").Offset(j, 0)
For k = 0 To 4

'msgbox x(j + 1, k + 1)

'msgbox .Range("B2").Offset(j, k * 2).Address
.Range("B2").Offset(j, k * 2) = x(j + 1, k + 1)


'msgbox y(j + 1, k + 1)
'msgbox .Range("B2").Offset(j, k * 2).Offset(0, 1).Address
.Range("B2").Offset(j, k * 2).Offset(0, 1) = y(j + 1, k + 1)
Next
Next
End With
line1:
End Sub
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I have tweaked the macro given below


Code:
Sub test()
Dim rng As Range, x(), y()
Dim j As Integer, k As Integer, cfind As Range
Dim m As Integer, n As Integer
'j = 0
'k = 0
Application.ScreenUpdating = False
With Worksheets("sheet1")
m = .Range("a1").End(xlDown).Row
n = .Range("a2").End(xlToRight).Column
'msgbox "m-1=  " & m - 1 & "  n-2=" & "  " & n - 1
ReDim x(1 To m - 1, 1 To 5)
ReDim y(1 To m - 1, 1 To 5)
'For j = 0 To m
For j = 1 To m - 2
Set rng = Range(.Range("B2").Offset(j, k), .Range("B2").Offset(j, k).End(xlToRight))
'msgbox rng.Address

For k = 0 To 4
'If j = m - 1 Then GoTo line2
x(j + 1, k + 1) = WorksheetFunction.Small(rng, k + 1)
Set cfind = rng.Cells.Find(what:=x(j + 1, k + 1), lookat:=xlWhole)
'msgbox cfind.Address
y(j + 1, k + 1) = .Cells(1, cfind.Column)

'msgbox x(j + 1, k + 1)
'msgbox y(j + 1, k + 1)
Next
k = 0
Next
End With
'line2:
With Worksheets("sheet2")



For j = 0 To m - 2
'If j >= m - 1 Then GoTo line1
.Range("A2").Offset(j, 0) = Worksheets("sheet1").Range("a2").Offset(j, 0)
For k = 0 To 4

'msgbox x(j + 1, k + 1)

'msgbox .Range("B2").Offset(j, k * 2).Address
.Range("B2").Offset(j, k * 2) = x(j + 1, k + 1)


'msgbox y(j + 1, k + 1)
'msgbox .Range("B2").Offset(j, k * 2).Offset(0, 1).Address
.Range("B2").Offset(j, k * 2).Offset(0, 1) = y(j + 1, k + 1)
Next
Next
End With
'line1:
Application.ScreenUpdating = True
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916

ADVERTISEMENT

Perhaps something like this spreadsheet solution, with carriers in Q1:AJ1, lanes in col P starting in row 3, and the data in Q3:AJ
A3 has the lowest bid for Lane1 and B3 is the carrier who made that bid.
D3 is the second lowest bid for Lane1 and E3 its carrier, etc.
The formulas can be dragged down to accomidate more rows.
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=25><b>A</b><td align=center width=49><b>B</b><td align=center width=45><b>C</b><td align=center width=25><b>D</b><td align=center width=49><b>E</b><td align=center width=45><b>F</b><td align=center width=25><b>G</b><td align=center width=49><b>H</b><td align=center width=45><b>I</b><td align=center width=25><b>J</b><td align=center width=49><b>K</b><td align=center width=45><b>L</b><td align=center width=25><b>M</b><td align=center width=49><b>N</b><td align=center width=45><b>O</b><td align=center width=56><b>P</b><td align=center width=23><b>Q</b><td align=center width=23><b>R</b><td align=center width=15><b>S</b><td align=center width=23><b>T</b><td align=center width=15><b>U</b><td align=center width=23><b>V</b><td align=center width=23><b>W</b><td align=center width=23><b>X</b><td align=center width=23><b>Y</b><td align=center width=23><b>Z</b><td align=center width=23><b>AA</b><td align=center width=23><b>AB</b><td align=center width=23><b>AC</b><td align=center width=23><b>AD</b><td align=center width=23><b>AE</b><td align=center width=23><b>AF</b><td align=center width=23><b>AG</b><td align=center width=23><b>AH</b><td align=center width=23><b>AI</b><td align=center width=23><b>AJ</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">carriers</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">A</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">B</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">D</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">E</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">F</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">G</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">I</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">J</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">K</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">L</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">M</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">N</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">O</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">P</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Q</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">R</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">S</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">T</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Bid</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">carrier</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Bid</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">carrier</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Bid</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">carrier</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Bid</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">carrier</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Bid</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">carrier</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">E</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">J</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">12</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">A</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">13</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">H</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Lane 1</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">12</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">33</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">15</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">17</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">18</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">13</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">20</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">22</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">23</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">24</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">25</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">26</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">28</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">29</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">30</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">31</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">E</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">N</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">12</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">A</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">15</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">D</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Lane 2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">12</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">33</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">15</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">17</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">18</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">26</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">20</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">22</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">23</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">24</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">26</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">27</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">28</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">29</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">30</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">31</FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>A3:A4<td align=center>A3 <td align = left >=SMALL($Q3:$AJ3,1)</tr>
<tr><td>N3:N4,K3:K4,H3:H4,E3:E4,B3:B4<td align=center>N3 <td align = left >{=INDEX($1:$1,1,SMALL(IF($Q3:$AJ3=M3,COLUMN($Q3:$AJ3)),COUNTIF($A3:M3,M3)))}</tr>
<tr><td>D3:D4<td align=center>D3 <td align = left >=SMALL($Q3:$AJ3,2)</tr>
<tr><td>G3:G4<td align=center>G3 <td align = left >=SMALL($Q3:$AJ3,3)</tr>
<tr><td>J3:J4<td align=center>J3 <td align = left >=SMALL($Q3:$AJ3,4)</tr>
<tr><td>M3:M4<td align=center>M3 <td align = left >=SMALL($Q3:$AJ3,5)</tr>
<tr><td colspan=3 align="center">Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac). </tr></table>
 

Baseball Guy

New Member
Joined
Apr 9, 2009
Messages
3
Perhaps something like this spreadsheet solution, with carriers in Q1:AJ1, lanes in col P starting in row 3, and the data in Q3:AJ
A3 has the lowest bid for Lane1 and B3 is the carrier who made that bid.
D3 is the second lowest bid for Lane1 and E3 its carrier, etc.
The formulas can be dragged down to accomidate more rows.
<TABLE cellSpacing=0 bgColor=#ffffff border=1><TBODY><TR bgColor=#aaaaaa><TD><TD align=middle width=25>A<TD align=middle width=49>B<TD align=middle width=45>C<TD align=middle width=25>D<TD align=middle width=49>E<TD align=middle width=45>F<TD align=middle width=25>G<TD align=middle width=49>H<TD align=middle width=45>I<TD align=middle width=25>J<TD align=middle width=49>K<TD align=middle width=45>L<TD align=middle width=25>M<TD align=middle width=49>N<TD align=middle width=45>O<TD align=middle width=56>P<TD align=middle width=23>Q<TD align=middle width=23>R<TD align=middle width=15>S<TD align=middle width=23>T<TD align=middle width=15>U<TD align=middle width=23>V<TD align=middle width=23>W<TD align=middle width=23>X<TD align=middle width=23>Y<TD align=middle width=23>Z<TD align=middle width=23>AA<TD align=middle width=23>AB<TD align=middle width=23>AC<TD align=middle width=23>AD<TD align=middle width=23>AE<TD align=middle width=23>AF<TD align=middle width=23>AG<TD align=middle width=23>AH<TD align=middle width=23>AI<TD align=middle width=23>AJ

<TR><TD align=middle bgColor=#aaaaaa>1<TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff>carriers<TD bgColor=#ffffff>A<TD bgColor=#ffffff>B<TD bgColor=#ffffff>C<TD bgColor=#ffffff>D<TD bgColor=#ffffff>E<TD bgColor=#ffffff>F<TD bgColor=#ffffff>G<TD bgColor=#ffffff>H<TD bgColor=#ffffff>I<TD bgColor=#ffffff>J<TD bgColor=#ffffff>K<TD bgColor=#ffffff>L<TD bgColor=#ffffff>M<TD bgColor=#ffffff>N<TD bgColor=#ffffff>O<TD bgColor=#ffffff>P<TD bgColor=#ffffff>Q<TD bgColor=#ffffff>R<TD bgColor=#ffffff>S<TD bgColor=#ffffff>T</TD>

<TR><TD align=middle bgColor=#aaaaaa>2<TD bgColor=#ffffff>Bid<TD bgColor=#ffffff>carrier<TD bgColor=#ffffff><TD bgColor=#ffffff>Bid<TD bgColor=#ffffff>carrier<TD bgColor=#ffffff><TD bgColor=#ffffff>Bid<TD bgColor=#ffffff>carrier<TD bgColor=#ffffff><TD bgColor=#ffffff>Bid<TD bgColor=#ffffff>carrier<TD bgColor=#ffffff><TD bgColor=#ffffff>Bid<TD bgColor=#ffffff>carrier<TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff><TD bgColor=#ffffff></TD>

<TR><TD align=middle bgColor=#aaaaaa>3<TD bgColor=#ffffff>2<TD bgColor=#ffffff>C<TD bgColor=#ffffff><TD bgColor=#ffffff>5<TD bgColor=#ffffff>E<TD bgColor=#ffffff><TD bgColor=#ffffff>5<TD bgColor=#ffffff>J<TD bgColor=#ffffff><TD bgColor=#ffffff>12<TD bgColor=#ffffff>A<TD bgColor=#ffffff><TD bgColor=#ffffff>13<TD bgColor=#ffffff>H<TD bgColor=#ffffff><TD bgColor=#ffffff>Lane 1<TD bgColor=#ffffff>12<TD bgColor=#ffffff>33<TD bgColor=#ffffff>2<TD bgColor=#ffffff>15<TD bgColor=#ffffff>5<TD bgColor=#ffffff>17<TD bgColor=#ffffff>18<TD bgColor=#ffffff>13<TD bgColor=#ffffff>20<TD bgColor=#ffffff>5<TD bgColor=#ffffff>22<TD bgColor=#ffffff>23<TD bgColor=#ffffff>24<TD bgColor=#ffffff>25<TD bgColor=#ffffff>26<TD bgColor=#ffffff>xx<TD bgColor=#ffffff>28<TD bgColor=#ffffff>29<TD bgColor=#ffffff>30<TD bgColor=#ffffff>31</TD>

<TR><TD align=middle bgColor=#aaaaaa>4<TD bgColor=#ffffff>2<TD bgColor=#ffffff>C<TD bgColor=#ffffff><TD bgColor=#ffffff>5<TD bgColor=#ffffff>E<TD bgColor=#ffffff><TD bgColor=#ffffff>5<TD bgColor=#ffffff>N<TD bgColor=#ffffff><TD bgColor=#ffffff>12<TD bgColor=#ffffff>A<TD bgColor=#ffffff><TD bgColor=#ffffff>15<TD bgColor=#ffffff>D<TD bgColor=#ffffff><TD bgColor=#ffffff>Lane 2<TD bgColor=#ffffff>12<TD bgColor=#ffffff>33<TD bgColor=#ffffff>2<TD bgColor=#ffffff>15<TD bgColor=#ffffff>5<TD bgColor=#ffffff>17<TD bgColor=#ffffff>18<TD bgColor=#ffffff>26<TD bgColor=#ffffff>20<TD bgColor=#ffffff><TD bgColor=#ffffff>22<TD bgColor=#ffffff>23<TD bgColor=#ffffff>24<TD bgColor=#ffffff>5<TD bgColor=#ffffff>26<TD bgColor=#ffffff>27<TD bgColor=#ffffff>28<TD bgColor=#ffffff>29<TD bgColor=#ffffff>30<TD bgColor=#ffffff>31</TD></TR></TBODY></TABLE><TABLE cellSpacing=0 bgColor=#ddedcc border=1>
<TBODY><TR><TD align=middle colSpan=3>Formulas in this range: </TD>

<TR><TD align=middle>Range with same formula<TD align=middle>Cell:<TD align=middle>holds Formula:</TD>

<TR><TD>A3:A4<TD align=middle>A3 <TD align=left>=SMALL($Q3:$AJ3,1)</TD>

<TR><TD>N3:N4,K3:K4,H3:H4,E3:E4,B3:B4<TD align=middle>N3 <TD align=left>{=INDEX($1:$1,1,SMALL(IF($Q3:$AJ3=M3,COLUMN($Q3:$AJ3)),COUNTIF($A3:M3,M3)))}</TD>

<TR><TD>D3:D4<TD align=middle>D3 <TD align=left>=SMALL($Q3:$AJ3,2)</TD>

<TR><TD>G3:G4<TD align=middle>G3 <TD align=left>=SMALL($Q3:$AJ3,3)</TD>

<TR><TD>J3:J4<TD align=middle>J3 <TD align=left>=SMALL($Q3:$AJ3,4)</TD>

<TR><TD>M3:M4<TD align=middle>M3 <TD align=left>=SMALL($Q3:$AJ3,5)</TD>

<TR><TD align=middle colSpan=3>Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac). </TD></TR>
</TABLE>

I will also test this formula...thank you for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top