Highlighting Rows when 2 columns don't match from another sheet.

[steveh8204]

Hi, firstly I'm not sure if the title explains very well my issue so I'll explain what the function needs to do.

I have two sheets with numerous columns on, some similar, some not. On my first sheet I need to look up the value in A1 for the similar value in the second sheet but I need to look up the value in Column B for the value as well and it needs to match column b on the second sheet.

The picture below probably better explains it.

https://drive.google.com/file/d/1qGJVvyL6wK0qbmlHk3Sb8t1F0_WD-Nem/view?usp=sharing

Thanks in advance for any help.

 

[oldbrewer]

PONUM

KCODE

pretend second sheet

PONUM

KCODE

PO1

CODE1

PO9

CODE9

VALUES IN K2:L10

PO2

CODE2

PO8

CODE88

PO3

CODE3

PO7

CODE7

PO4

CODE4

PO6

CODE6

PO5

CODE5

PO5

CODE55

PO6

CODE6

PO4

CODE4

PO7

CODE7

PO3

CODE33

PO8

CODE8

PO2

CODE2

PO9

CODE9

PO1

CODE1

PO8,PO5,PO3 ARE COLOURED RED BY THIS CONDITIONAL FORMATTING FORMULA

IN K2

=OFFSET($A$1,MATCH(K2,$A$2:$A$10,0),1)<>L2

<colgroup><col span="17"></colgroup><tbody>
</tbody>

 

[brownbread]

Imperfect answer: Add conditional formatting with this formula, to the cells in both columns on the sheet you want to highlight. This assumes your data is in columns A and B starting on row 2, and that your second sheet is called Sheet2, and that your second sheet data appears on rows 2 to 10:

=IF(MATCH($A2,Sheet2!$A$2:$A$10,0)=MATCH($B2,Sheet2!$B$2:$B$10,0),1,0)

Edit: Didn't realise answer already provided... AND my answer won't work if a there are non-unique matches in either column... to get a match in line an array formula works nicely, =VLOOKUP(A2&B2,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,1,0) (enter with CTRL+SHIFT+enter)

 

[oldbrewer]

BROWNBREAD = I supplied a methodology to the OP and assumed he could cond format the other column if required

 

[steveh8204]

Imperfect answer: Add conditional formatting with this formula, to the cells in both columns on the sheet you want to highlight. This assumes your data is in columns A and B starting on row 2, and that your second sheet is called Sheet2, and that your second sheet data appears on rows 2 to 10:

=IF(MATCH($A2,Sheet2!$A$2:$A$10,0)=MATCH($B2,Sheet2!$B$2:$B$10,0),1,0)

Edit: Didn't realise answer already provided... AND my answer won't work if a there are non-unique matches in either column... to get a match in line an array formula works nicely, =VLOOKUP(A2&B2,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,1,0) (enter with CTRL+SHIFT+enter)

I've used this one since these is the one I understand the most to adapt but I've been hit with an error message when I've created this in Conditional Formatting:

"You may not use reference operators or array constants for Conditional Formatting criteria".

I didn't understand exactly what I was supposed to put where for the other answer if I'm honest.

One of the sheet is called "SAP Korozo" and has the "PO Number" in Column A and "SAP Code" in B. The other sheet is called "Korozo" and has the "PO Number" in Column D and the "K Number" in Row A (which is the other way around from the first which probably doesn't help. The rows start at Row 3 and I was hoping to highlight the odd ones out on sheet (Korozo).

Thanks for your assistance with this matter.

 

[oldbrewer]

in sheet korozo in the first cell with a po number this cond formatting

offset(sapkorozo!$a$1, match(d2,sapkorozo!$a$2:$a$20,0),1)<>a2

 

[steveh8204]

in sheet korozo in the first cell with a po number this cond formatting

offset(sapkorozo!$a$1, match(d2,sapkorozo!$a$2:$a$20,0),1)<>a2

Thanks for the quick reply. Looks ok except my work laptop battery died half way through checking so will have to wait til Monday. I assume to do the same with the K Codes I just do similar formatting in the K Code column and change the column references?

Thanks again for your help.

 

[oldbrewer]

use the same formula - basically - you just locate the formula in the other cell you want to color....

 

[steveh8204]

use the same formula - basically - you just locate the formula in the other cell you want to color....

Nice one. Thanks again for all your help.

 

[oldbrewer]

I am 73, retired, this forum keeps my brain cells ticking over.....(just)