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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Coupon[/TD]
[TD]Rate[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]3.25[/TD]
[TD]98.335[/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]3.375[/TD]
[TD]99.032[/TD]
[/TR]
[TR]
[TD]2.5[/TD]
[TD]3.50[/TD]
[TD]99.600[/TD]
[/TR]
[TR]
[TD]3.0
[/TD]
[TD]3.25
[/TD]
[TD]97.101
[/TD]
[/TR]
[TR]
[TD]3.0
[/TD]
[TD]3.375
[/TD]
[TD]98.289
[/TD]
[/TR]
[TR]
[TD]3.0
[/TD]
[TD]3.50
[/TD]
[TD]99.413
[/TD]
[/TR]
[TR]
[TD]3.0[/TD]
[TD]3.625[/TD]
[TD]100.472[/TD]
[/TR]
[TR]
[TD]3.0[/TD]
[TD]3.75[/TD]
[TD]101.328[/TD]
[/TR]
[TR]
[TD]3.0[/TD]
[TD]3.875[/TD]
[TD]101.923[/TD]
[/TR]
[TR]
[TD]3.0[/TD]
[TD]4.00[/TD]
[TD]102.404[/TD]
[/TR]
[TR]
[TD]3.5
[/TD]
[TD]3.75
[/TD]
[TD]100.214
[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Book1, Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Book2, Sheet1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A (Rate)[/TD]
[TD]B (Price)[/TD]
[TD][/TD]
[TD]A (Rate)[/TD]
[TD]B (Price)[/TD]
[/TR]
[TR]
[TD]3.25[/TD]
[TD]98.335[/TD]
[TD][/TD]
[TD]4.00[/TD]
[TD]102.404[/TD]
[/TR]
[TR]
[TD]3.375[/TD]
[TD]99.032[/TD]
[TD][/TD]
[TD]3.875[/TD]
[TD]101.923[/TD]
[/TR]
[TR]
[TD]3.5[/TD]
[TD]99.600[/TD]
[TD][/TD]
[TD]3.75[/TD]
[TD]101.328[/TD]
[/TR]
[TR]
[TD]3.25
[/TD]
[TD]97.101
[/TD]
[TD][/TD]
[TD]3.625[/TD]
[TD]100.472[/TD]
[/TR]
[TR]
[TD]3.375
[/TD]
[TD]98.289
[/TD]
[TD][/TD]
[TD]3.50[/TD]
[TD]99.600[/TD]
[/TR]
[TR]
[TD]3.5
[/TD]
[TD]99.413
[/TD]
[TD][/TD]
[TD]3.375[/TD]
[TD]99.032[/TD]
[/TR]
[TR]
[TD]3.625[/TD]
[TD]100.472[/TD]
[TD][/TD]
[TD]3.25[/TD]
[TD]98.335[/TD]
[/TR]
[TR]
[TD]3.75[/TD]
[TD]101.328[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.875[/TD]
[TD]101.923[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4.00[/TD]
[TD]102.404[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.75
[/TD]
[TD]100.214
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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