Need some help on Conditional Formatting

**Leon86** · 10-27-2011, 02:35 AM

Hi everybody,

This is my problem, I have two tables of values, 2nd table to compare against table 1 values with cells being highlighted if values of the 2nd table are higher than that of the first.

However, because I have certain duplicates for some data, my formula for conditional formatting only detects the first data and totally misses the 2nd one. Its kind of hard to narrate my problem so I have attached a sample of it.

Thanks if you are viewing this.

**Marcol** · 10-27-2011, 05:22 AM

Try
Highlight E8:E12
Conditional Formatting > New Rule > Use Formula to ....
Format values where this value is true

=E8>INDEX($B$2:$E$5,MATCH(B8,$A$2:$A$5,0),MATCH(D8,$B$1:$E$1,0))

Format Cells > Your Choice.

**Leon86** · 10-27-2011, 05:49 AM

Thanks for your fast response. It is more or less what I wanted however, how would I need to edit it cause i actually want it compared in opposite ways. The table at the bottom would be where I input the data to be compared against the table at the top which would be highlighted. So the problem which I had was that Holland CC & Holland TP needs to be compared against one value at the top only.

Thanks for everything againz

**Marcol** · 10-27-2011, 07:22 AM

If I read you correctly I would use a helper table, this can be hidden using the grouping button.

See if this workbook helps.

**Leon86** · 10-28-2011, 05:48 AM

Sorry for the late reply. Is it possible in your solution to not have the result table and hence just the two tables from the original, which means end of the day I am only left with only two tables only.

Thanks Alot !!

**Marcol** · 10-28-2011, 06:02 AM

It's possible but would be quite complicated and in all probability much heaviery on memory with larger tables.

You could put the result table on a seperate, hidden, sheet if that would suit you better.

**Leon86** · 10-28-2011, 06:30 AM

Will do. Thanks Againz much appreciated !

**Marcol** · 11-02-2011, 07:06 AM

In reply to yuor PM regarding extending this table.

Unfortunately I was a bit careless this is solution.
The formula in H2 should have been

=MAX(MAX(IF($B$8:$B$12&$D$8:$D$12=$B$1&D$1,$E$8:$E$12,"")),MAX(IF($B$8:$B$12&$D$8:$D$12=$D$1&B$1,$E$8:$E$12,"")))

Confirm with Ctrl+Shift+Enter
Drag across as required.
Copy this to I3 and drag the references to B1 & C1 to C1 & D1, confirm the formula then drag across.
Repeat this pattern as required.

I have replaced the attachment in post #4 with a corrected workbook.