Formula that will check multiple criteria

[geliedee]

Hi all,

i have here a scenario which i will be needing your help.

Capture.JPG

I have here 5 columns.
Column A - Customer Number
Column B - Function
Column C - Customer Number
Column D - Remarks


So here's the condition.
In column B - Function, this contains mandatory function which is BP, PY, SH and BP. And The customer number should be always equal to its self when the function is BP, PY, SH and SP. If the customer number is not equal to itself for this four partner functions then the remarks should be REPLACE.

But there will cases that the SH or BP will be multiple. And the remarks would be UNLINK but i should have the four mandatory function.
And there will be like YA, YC and YG remarks should be UNLINK also. Book1.xlsx


Anybody have an idea? It makes me so sick since i have like 500 line items to check.

Attached is file.
Any help will be appreciated.

[azumi]

Not sure but try this..........

[geliedee]

Hi azumi!

Just checked and it works like a charm!
How ever, i have to add YJ to the formula... Can you still help me?

Please Login or Register  to view this content.

[azumi]

Just add condition on blue highlited text:

=IF(INDEX($A$2:$A$20,MATCH(A2,$A$2:$A$20,0))=C2,"",IF(OR(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,
B2="YA",B2="YC",B2="YG";B2="YJ"),"Unlink","Replace"))

[geliedee]

Hi Azumi,

That works on single customer but now i have duplicated customers with should be identified. When i applied your formula, it doesn't give me the correct output since they are appeared like 8 times but from same Sales Org but different Distribution and Div. And i am not getting the expected result. Since i have to apply this formula into thousands of customers

Please see attached.

[newdoverman]

I think that the formula can be shortened to this.

Please Login or Register  to view this content.

This part "IF(INDEX($A$2:$A$20,MATCH(A2,$A$2:$A$20,0))=F2,"",.... will find the first match for A2 in the range $A$2:$A$20 which will be A2. Therefore, the formula comes down to IF(A2=F2,"",........

[azumi]

Spot on @newdoverman.....


Sorry for late reply

[geliedee]

Hi Azumi,

i have checked both of your formula but it still doesnt show correct results

Since the customers are duplicated into another sales area, it shows unlink instead of replace.

can we actually include the sales org distribution channel and division to the formula?
Sorry i am not good at excel and i just want to shorten the burden im facinbg....

Attached is the file

[azumi]

Sorry for late reply, hope this works for u

[geliedee]

Hi Azumi!!

Thats what i was looking for! Omg thank you so much hope i can apply this to my thousand of customers!

[azumi]

Need edit for formula:

=IF(OR($E2="YA",$E2="YC",$E2="YG",$E2="YJ"),"Unlink",IF($A2=$F2,"",IF(INDEX($A$2:$A$27,MATCH($D2,$D$2:$D$27,0))<>$F2,IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2,$E$2:E2,E2)=1,"Replace","Unlink"),"")))

sorry always forget something........

[geliedee]

Hi azumi, thank you so much!!!