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

steveh8204

Board Regular
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

Board Regular
PONUMKCODEpretend second sheetPONUMKCODE
PO1CODE1PO9CODE9VALUES IN K2:L10
PO2CODE2PO8CODE88
PO3CODE3PO7CODE7
PO4CODE4PO6CODE6
PO5CODE5PO5CODE55
PO6CODE6PO4CODE4
PO7CODE7PO3CODE33
PO8CODE8PO2CODE2
PO9CODE9PO1CODE1
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

New Member
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)
 
Last edited:

oldbrewer

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

steveh8204

Board Regular
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

Board Regular
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

Board Regular
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top