matching

[ntudev]

Hello, can anyone help me with the below? i need i'm guessing a formula that will do the below. I need the last column to show match as long as ZIP & ZIP matches PIZ & PIZ1 even if the row is blank and if 1 number is off just say its not a match.

ZIP	ZIP1			PIZ	PIZ1	does it match?
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412					match
259530	311412					match
259530	0					not match

[DataNath]

Do you mean like this? Workbook attached.

[CarliE]

@ntudev if you need those nulls to match here is my formula :

if [ZIP]=[PIZ] and [ZIP1]=[PIZ1] then "match" elseif length([ZIP])!=1 and Length([ZIP1])!=1 and IsEmpty([PIZ]) and IsEmpty([PIZ1]) then "match" else "not match" endif

[binuacs]

@ntudev one way of doing this with the find and replace tool

#

[ntudev]

its good, but i need it to match the blank lines as long as its there

[CarliE · Accepted solution]

@ntudev , does my solution above solve that?

[DataNath]

@ntudev can you expand more on the logic?

'as long as ZIP & ZIP matches PIZ & PIZ1 even if the row is blank' - if there are blanks, ZIP & ZIP1 won't match PIZ & PIZ1 and so we can't consistently check for a match, especially if some numbers are different to the majority in that field.

'if 1 number is off just say its not a match' - if 1 number is off against what? Especially considering that, if any are blank then it'll always be off, even if it's the same as the majority in that column.

[ntudev]

yes it does.

[ntudev]

OK as i was working on this i found another challenge. How would i do the below? Note ZIP and PIZ should always equal to work. Can anyone help?

ZIP	ZIP1			PIZ	PIZ1	does it match?
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530	311412	match
259530	311412			259530		match
259530	311412			259530		match
258888	311412			258888	159974	not match
259999	0			259999	311412	New
251000	3114500			251000		New
251001	320700					New