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 |
解決済! 解決策の投稿を見る。
@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
its good, but i need it to match the blank lines as long as its there
@ntudev , does my solution above solve that?
@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.
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 |