Conditionnal Formating based on text, help Thanks!

[stan1020]

Hello,

I have a huge file of 60000 rows and I need to complete a column (B) named "Marker" with different social entities by looking in an other column (A) with different counterparts names.
For example:
if counterpart = AVIS then Marker = AA
if counterpart = BUBU then Marker = BB
if counterpart = BIBU then Marker= CC

For those it is ok, but I have also other cases of condition, if the 3 first letters of my counterpart are "LBF" (for example LBF5875 or LBF2545) then the marker will be also AA, and it is the same if the 2 first letters of my counterpart are "DP" (for example DPEA or DPOS), then the marker should be completed with "BB"

Last condition: if the counterpart names does not match with one of the conditions, it has not to be completed

I have tried different means to compile all this conditions yet it is not working at all.
Some times I have one conditions completed by my macro, sometimes nothing...

Does anybody has ever seen this situation, please, or could someone help me?
It would be really useful for me

Thank you!

Stan

 

[RalphA]

For your stated conditions, this seems to work for me:

=IF(OR(A1="AVIS",LEFT(A1,3)="LBF"),"AA",IF(OR(A1="BUBU",LEFT(A1,2)="DP"),"BB",IF(A1="BIBU","CC","")))

Let us know if this is what you are looking for.

 

[stan1020]

Thanks a lot!

Thanks RalphA

it is working perfectly, sometimes the easiest way to solve a problem is the less complicated!

Thanks again

Stan

 

[RalphA]

I'm happy that my suggestion was helpful to you!