Excel Conditional formatting tie breaking

Question

I have a spreadsheet of prices from a selection of suppliers, with one supplier per column.

// Conditional Format code
=D2=MIN($D2:$K2)

I have used conditional formatting to highlight the cheapest value (black). Then, I use Macro/Basic code to filter and export based on the highlighted colour to make order lists for each supplier. Everything works fine so far.

In the image, two suppliers are highlighted on the same row at £0.17. When I run my code to export the highlighted lines, it will include it in both supplier orders. I only want one highlighted value to add to a single supplier's order.

The problem is that when values for the same product are equal, both (or more) suppliers are highlighted. This results in duplicate orders being exported. I want a way to tie-break these duplicate values, so only one value is highlighted, according to a preference order (Supplier A, then B, C, D, etc.).

This is the code I currently use. It filters down the suppliers and is run each time with AutoFilter Field= changed for each supplier column:

  ActiveSheet.Range("$A$1:$P$1219").AutoFilter Field:=4, Criteria1:=RGB(0, 0 _
        , 0), Operator:=xlFilterCellColor
      ActiveSheet.Range("$A$1:$S$1219").AutoFilter Field:=18, Criteria1:="<>0", Operator:=xlFilterValues
    '
    'Product&Pack / Price / Quantity''
    Range("A2:B25,C2:C25,R2:R25").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Branch1").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
''Reset Filters''
    Sheets("NEW2025").Select
    ActiveSheet.ShowAllData

I have tried a few solutions I found via Google, such as adding a very small difference to each column value (e.g., dividing by 0.0001) to create a tie-breaker, but I couldn't get it to work properly.

If somebody could help, or even point me in the right direction, that would be greatly appreciated.

Thanks in advance.

When I read your question, this is the first thing on my mind: stackoverflow.com/questions/10714251 — Dominique
– Dominique, Commented Nov 26 at 14:09

user1016274 · Accepted Answer · 2025-11-29 22:51:26Z

2

As the condition, use

=COLUMNS($D1:D1)=MATCH(MIN($D1:$K1);$D1:$K1;0)

The MATCH() function will select the first value in the range given, so that suppliers are marked from left to right, and only one at a time. Your VBA code would not need any changes.

edit: left side should only evaluate to 1,2,3..., denoting the position within the range given (here for example D1:K1). Thanks to barry houdini for providing a solution to this.

edited Nov 29 at 22:51

answered Nov 26 at 14:43

user1016274

4,2992 gold badges25 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

barry houdini Nov 27 at 19:54

The concept is good but this doesn't quite work because COLUMN(D1)=4 and you need to be starting at 1 not 4, so I would suggest using COLUMNS function instead, i.e. =COLUMNS($D1:D1)=MATCH(MIN($D1:$K1),$D1:$K1,0) That will highlight only the first min value if there are duplicates

Adrian Gage Nov 28 at 9:33

Thanks for the help, that works perfectly!

user1016274 Nov 29 at 22:52

You are absolutely right, it worked for me because my range started in column 1. I've corrected my answer with a reference to your post. Thanks a lot.

Collectives™ on Stack Overflow

Excel Conditional formatting tie breaking

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related