CODEWITHSUNDEEP
excelconditional-formatting
smartini
smartini

Reputation: 455

Conditional Formatting - Multi-conditional, InStr-check, lowest value (part 2)

I asked a question back in 2019 and I remember I was asked once to create a just a new question instead of pulling the old one back and ask a second one in there. --> Conditional Formatting - Multi-conditional, InStr-check, lowest value

My use case needs to be adjusted and I am struggling again with that one. If have the following ListObject/Table in my Excel Spreadsheet and I want to highlite the cheapest price for each row. This works quite well with the following conditional formatting formula: enter image description here

What I additionally need is that whenever there is a value in a Column starting with "AAA" or "XXX" (in this case column [H], [I] or [J]) this value should be highlighted, eventhough there might be a lower value in another column. But this rule should only be applied if in column [D] the value is "A". For simplicity those special treated columns are always starting from column [H] and ongoing. They are not occuring in between somewhere.

It does not occur that both column [H] and [I] are filled. It is always either or. But column [J] can have values additionally.

What I have so far:

enter image description here

=$D3="A"

Formats cells with NO HIGHLIGHTNING, applied to columns [J] to [AC] as an extra rule above the next rule below, every cell after Range [H:J] will be highlighted white when column [D] has value "A".

@ProfoundlyOblivious helped me with the major formula:

=IFERROR(AND(H3>0,H3=SEARCH("A",$D3)*H3*1/SEARCH("AAA",H$2)^-1),IF(H3<>"",H3=MIN($H3:$AC3),""))

What is not working is that in row 2184 to 2189 for example there is no highliting at all. It should highlight the values in column [H] because they are clearly the lowest one. But somehow the major formula needs to be adjusted.

edit: my try was this one, but it misses some rows. The dark green ceels in the rows 2184 to 2189 are visible now, but it misses some if the cheapest price is in column [I] though.

=IF(J3<>"",J3=MIN($H3:$J3),"")

enter image description here

Upvotes: 0

Views: 105

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

You can use a single rule with the following formula:

=LOOKUP(2,1/FREQUENCY(0.01,
($H3:$T3)*((($D3<>"A")+(LEFT($H$2:$T$2,3)="AAA")+(LEFT($H$2:$T$2,3)="XXX"))>0)),
COLUMN($H$2:$T$2))=COLUMN()

Sample result with some random data: enter image description here

FREQUENCY returns an array similar to: {0,0,0,1,0,0,0,0,0,0,0} where 1 indicates the position of the cell in row with the smallest value greater than or equal to 0.01 (use 1E-300 if you have values lower than 0.01)

Multiplication by ((($D3<>"A")+(LEFT($H$2:$T$2,3)="AAA")+(LEFT($H$2:$T$2,3)="XXX"))>0) zeroes all cells that do not meet any of the conditions:

  • row header is equal to "A"
  • column header starts with "AAA" or "XXX"

LOOKUP ignores 1/0 errors and finds the position of the only 1 returned by FREQUENCY and returns corresponding column number.

Upvotes: 1

Related Questions