Comparing duplicate values, finding MIN, conditional formatting

[shiftdelete]

I've tried a few methods, but I'm struggling to put them all together to get my exact scenario to work. Any help is greatly appreciated.

I have a spreadsheet that needs to search down column B (Rate), find any duplicate values (there will be 12-15 duplicate pairs)-- and compare the values of Column C (Price) between the duplicate pairs and get the smallest value. (I'm using this to do conditional formatting - changing font color so that it looks greyed out.)

See the example below-- I would like to apply the formatting to columns A, B & C to dim the font color. (or at LEAST column C.) I cannot have the entire row highlighted, because I have additional non-related data in other columns.

Coupon	Rate	Price
2.5	3.25	98.335
2.5	3.375	99.032
2.5	3.50	99.600
3.0	3.25	97.101
3.0	3.375	98.289
3.0	3.50	99.413
3.0	3.625	100.472
3.0	3.75	101.328
3.0	3.875	101.923
3.0	4.00	102.404
3.5	3.75	100.214

<tbody>
</tbody>

 

[VBA Geek]

you can use this conditional formatting rule


=IF(COUNTIF($B$2:$B$12,$B2)>1,MIN(IF($B2=$B$2:$B$12,$C$2:$C$12))=$C2,FALSE)

Excel 2013
	A	B	C
1	Coupon	Rate	Price
2	2.5	3.25	98.335
3	2.5	3.375	99.032
4	2.5	3.5	99.6
5	3	3.25	97.101
6	3	3.375	98.289
7	3	3.5	99.413
8	3	3.625	100.472
9	3	3.75	101.328
10	3	3.875	101.923
11	3	4	102.404
12	3.5	3.75	100.214
Sheet1

 

[shiftdelete]

Thanks VBA Geek!!

I have 1 follow-up question that is based on the same data.

I have a separate excel workbook that I am linking to this one. Column A is rate. Column B is price.

How can I pull the list of Rates & Prices, removing the MIN of any duplicates?

 

[VBA Geek]

hi, no problem.

could you post again the the tables and the desired results? you can put both tables on the same sheet for convenience

 

[shiftdelete]

Book1, Sheet1			Book2, Sheet1
A (Rate)	B (Price)		A (Rate)	B (Price)
3.25	98.335		4.00	102.404
3.375	99.032		3.875	101.923
3.5	99.600		3.75	101.328
3.25	97.101		3.625	100.472
3.375	98.289		3.50	99.600
3.5	99.413		3.375	99.032
3.625	100.472		3.25	98.335
3.75	101.328
3.875	101.923
4.00	102.404
3.75	100.214

<tbody>
</tbody>

Book2,Sheet1 will externally reference Book1,Sheet1 and display a list in descending order of all Rates, taking the Max Price for any duplicate Rate Values. Does that make sense? I could have this list made on a new sheet in Book1 if it is too hard to create the formula using an external reference.

 

[VBA Geek]

You'd do it like this


<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">{=LARGE(<font color="Blue">A$2:A$12,SUM(<font color="Red">1,COUNTIF(<font color="Green">A$2:A$12,D$1:D1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$D2=A$2:A$12,B$2:B$12</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013
	A	B	C	D	E
1	RATE	PRICE		RATE	PRICE
2	3.25	98.335		4	102.404
3	3.375	99.032		3.875	101.923
4	3.5	99.6		3.75	101.328
5	3.25	97.101		3.625	100.472
6	3.375	98.289		3.5	99.6
7	3.5	99.413		3.375	99.032
8	3.625	100.472		3.25	98.335
9	3.75	101.328
10	3.875	101.923
11	4	102.404
12	3.75	100.214
ACCESS