How to extract the name of 2nd bidder

[Zaigham]

Hi All,
In the following example I have extracted the name of lowest bidder with the help of =INDEX() =MIN() and =MATCH() functions. It also works with =INDEX() and =SMALL() functions. In some cases same rate is quoted by more than one bidder. In this situation =SMALL() returns correct rate i.e., 2nd small value but I am unable to extract the name of 2nd bidder who had quoted the same rate.
How can I extract the name of 2nd or may be 3rd bidder who quoted same rates.

Excel Workbook
	A	B	C	D	E	F
6	Name of Firm =>	LFT Traders	Recho Enterprises	True Fit Shoes	Madni Builders	Gems & Stones Ltd.
7	BOQ	* * * * * * 200,000	* * * * * * 200,000	* * * * * * 200,000	* * * * * * 200,000	* * * * * * 200,000
8	Premium Quoted	* * 2.00 % Above	* 1.00 % Above	* 1.00 % Above	* * 2.00 % Above	* * 3.00 % Above
9	*	*	*	*	*	*
10	Amount of Premium	4,000	2,000	2,000	4,000	6,000
11	Tender Cost	204,000	202,000	202,000	204,000	206,000
12	*	*	*	*	*	*
13	*	*	* * *1.00 % Above	MIN(B8:AE8)	*	*
14	*	*	2	MATCH(C23,B8:AE8,0)	*	*
15	*	*	Recho Enterprises	INDEX($B$6:$AE$6,C24)	*	*
16	*	*	*	*	*	*
17	*	*	1	SMALL(B8:AE8,1)	*	*
18	*	*	2	MATCH(C23,B8:AE8,0)	*	*
19	*	*	Recho Enterprises	INDEX($B$6:$AE$6,C18)	*	*
20	*	*	*	*	*	*
21	*	*	1	SMALL(B8:AE8,2)	*	*
22	*	*	2	MATCH(C21,B8:AE8,0)	*	*
23	*	*	Recho Enterprises	INDEX($B$6:$AE$6,C22)	*	*
Work (1)

Regards
Zaigham

 

[Bradium]

Hi

I have uploaded a file that I think will help you. Just change the rank at A1 and watch your results change.

http://www.4shared.com/document/u5fg3Z_1/Find_rank.html

Regards

Brad

 

[Zaigham]

Hi Bradium
Thanks for your response. I will try to use your technique for my purpose.
Regards
Zaigham

 

[Zaigham]

Hi
I have downloaded the file of brother Bradium. It is good but do not solve my problem. Sample data is again copied here. All formulas of the table have been converted into values just to avoid the lengthy explanations by HTML Jeanie. I want the sentence appearing at B14 to be appeared at B13 if the number of lowest bidders is greater than one.

Excel Workbook
	A	B	C	D	E	F
6	Name of Firm =>	LFT Traders	Recho Enterprises	True Fit Shoes	Madni Builders	Gems & Stones Ltd.
7	BOQ	* * * * * * 200,000	* * * * * * 200,000	* * * * * * 200,000	* * * * * * 200,000	* * * * * * 200,000
8	Premium Quoted	* * 2.00 % Above	* 1.00 % Above	* 1.00 % Above	* * 2.00 % Above	* * 3.00 % Above
9	*	*	*	*	*	*
10	Amount of Premium	4,000	2,000	2,000	4,000	6,000
11	Tender Cost	204,000	202,000	202,000	204,000	206,000
12	*	*	*	*	*	*
13	NOTE:	M/s. Recho Enterprises is the LOWEST bidder by quoting his rates @ 1.00%	*	*	*	*
14	Required Text:	M/s Recho Enterprises and True Fit Shoes both are LOWEST bidder by quoting their rates @ 1.00%	*	*	*	*
Work (1)

I am sure that many of the forums members have the solution.

Awaiting for expert's advise.
Zaigham

 

[Bradium]

Hi Zaigham

I have adjusted the example for you and included some vba as another solution.

Regards

Brad

http://www.4shared.com/document/yQTVXvzu/Find_rank_update.html

The code in the example is

Sub myrank()

mymin = Application.Min(Range("b4:g4"))

For Each c In Range("b4:g4")
If c = mymin Then
i = i + 1
End If

Next

Select Case i

Case 1

For Each c In Range("b4:g4")
If c = mymin Then
MyName = c.Offset(-2, 0)
Mybuild = Mybuild & " " & MyName
End If

Next c

Mybuild = "Ms " & Mybuild & " is the LOWEST bidders by quoting his rate @ " & Application.Text(mymin, "0.00%")

MsgBox (Mybuild)


Case 2 To 10

For Each c In Range("b4:g4")
If c = mymin Then
MyName = c.Offset(-2, 0)
Mybuild = Mybuild & " " & MyName & Chr(10)
End If

Next c

Mybuild = "Ms " & Chr(10) & Mybuild & " are the LOWEST bidders by quoting their rates @ " & Application.Text(mymin, "0.00%")

MsgBox (Mybuild)
End Select
End Sub

 

[Zaigham]

Brother Brad

Just thanks is not enough. So kind of you. I have found this forum very helpful where many other kind persons like you always provide help. Now I can modify it as per my requirements.
Once again a lot of Thanks.