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.

 

[steveh8204]

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

And that brain is very much appreciated. I don't suppose you can give me the formula as it needs to be in the other cell (in the Korozo Sheet), I've tried changing the cell references myself and I seem to have got confused with it and it doesn't work.

Your 73yo brain appears to work a lot better than my 43yo one.haha

 

[oldbrewer]

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

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

the 1 and 2 towards the end of the formula are how many columns to the right to go
put the lower cond format formula into the other cell you want to color

 

[oldbrewer]

and of course the a2 needs to change to reflect where the cell is

 

[steveh8204]

I must be missing something as I can't get it work. I think it's partly because my brains fried after the day I've had and partly because I am absolutely dire at CF haha

Would you be so kind as to apply the formatting to the file attached? I would be extremely grateful.

Basically the PO 4501800488 in "SAP Korozo" relates to K183633060 but in the sheet "korozo" it is K183633056 (both highlighted in attached). The PO should match the K Code always (except when the supplier (sheet korozo) has applied the wrong promo (last three digits).

I really appreciate any help as I've started a new job which this sheet helps with. I used to spend a bit of my own time learning VBA etc but I haven't had much chance to lately with learning a new role. I'm all learnt out by the evening.

https://www.dropbox.com/s/3l5x4swxkxyr8r2/Wrong K Pin.xlsx?dl=0

 

[oldbrewer]

=and(match(j5,$a$3:$a$5,0)>0,offset($a$2,match(j5,$a$3:$a$5,0),1)<>g5)

 

[steveh8204]

Right I've been through this again and I still can't seem to get this to work. I've lost what I need to put where and all sorts. Sorry for being thick but in the file in the link below are you able to show me what you mean.

The value in sheet "SAP Sheet" B10 is different to the value in B6 in "Sheet2" even though they both refer to the same PO (4501782524). I've highlighted in yellow to make it easier to see. This shouldn't be the case as the both values should be the same. I want to make it easier to see which ones don't match as a (human) error has occurred if so.

Thanks in advance for any help.

https://drive.google.com/open?id=1BBQxNJ501XQrYjgCpw9OASZJ90yfRDXa

 

[oldbrewer]

ident	color
1	red
2	blue
3	green
4	red
5	blue
6	green
7	red
8	blue
9	green
10	red
				ident	color	marker col
				2	blue
				5	blue
				8	blue
				6	green
				9	green
				3	purple	mismatch
				1	red
				4	red
				7	red
				10	red
			we need to detect that ident 3 in lower table
			does not match with upper table
			first cell in marker column has this formula
			=IF(OFFSET($A$1,MATCH(E14,$A$2:$A$11,0),1)<>F14,"mismatch","")
			I know your columns are laid out differently
			but you just need to adjust the formula

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[steveh8204]

ident	color
1	red
2	blue
3	green
4	red
5	blue
6	green
7	red
8	blue
9	green
10	red
				ident	color	marker col
				2	blue
				5	blue
				8	blue
				6	green
				9	green
				3	purple	mismatch
				1	red
				4	red
				7	red
				10	red
			we need to detect that ident 3 in lower table
			does not match with upper table
			first cell in marker column has this formula
			=IF(OFFSET($A$1,MATCH(E14,$A$2:$A$11,0),1)<>F14,"mismatch","")
			I know your columns are laid out differently
			but you just need to adjust the formula

<tbody>
</tbody>

Right, Its took me a while but it seems to match the formula you put (obviously updated with references) but it still doesnt work. All of them come up as mismatches:

=IF(OFFSET('SAP Sheet'!$A$1,MATCH(F3,'SAP Sheet'!$A$3:$A$100,0),1)<>B3,"mismatch","")

From your formula the ref $A$1 I didn't fully understand so left as is. The ref F3 and table reference $a$3:$A$100 seems to match yours as well as B3 but still no joy. I left the offset as 1 as it was still that in the reference table.

 

[oldbrewer]

your offset is =offset(a1,match(f3,a3:a100,0),1)

why is not a2:a100
is f3 the same type of data as a3 ie both dates, or both text, or both variable numbers ?

I am away for 8 days now so cannot help further till I am back

 

[steveh8204]

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

<tbody>
</tbody>

I've just had time to have a proper look at what I'missing with this. I've managed to get this working when I copy and paste into a new sheet and format as per your reply above. The problem comes when I try to adapt it to my sheet, even after changing a few things.


To help I could do with understanding a couple of things. What is the $A$1 at the beginning of the formula supposed to be? In your sheet it is just a heading.


Also do I need to qualify all references with the sheet names?


I've put the formula in the sheet below. Could you check it please as it's a cut and paste of what you said but it doesnt work. It highlights random ones which do appear but not one (cell A79 in Korozo) that should be highlighted.


Any help would be appreciated as I've spent the last two hours going through the whole thread and got nowhere.

https://drive.google.com/open?id=1bOMuj4IXYt8pc7Qsf4ArCgbFAJXuTw-p