• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in Excel formula to address FullSalutation

Madhuri_G

New Member
Hi Chandoo/Brilliant Guys out there..

I just want to know answer for the below scenario... I tried this formula but it's not working. Can you please help me guys..

I have data in 8SEGMENTS with contacts FULLSalutation, for contacts under 18 they should be addressed as Lastname + Familyname.
For records with if name has one initial, or initials, or dots(anomalies) it should change to 'Supporter'

I'm getting error for one initial, or initials, or dots(anomalies)

Can you please rectify this??
=IF(AND(AK5<18,AK5<>""),CONCATENATE(H5," ","Family",""),IF(OR(CD5="SEGMENT_1",CD5="SEGMENT_2",CD5="SEGMENT_3",CD5="SEGMENT_4",CD5="SEGMENT_5",CD5="SEGMENT_6",CD5="SEGMENT_7",CD5="SEGMENT_8"),
IF(BJ5,"Supporter",
IF(OR(I5="",I5=".",I5="Unknown",LEN(TRIM(I5))=1),
E5&" "&H5,
I5)
),
))
 

AlanSidman

Active Member
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.
 

Madhuri_G

New Member
Hi Alan,
Thanks so much for quick reply,, I have attached sample file, Before and After(which I manually did ) It should be the ACTUAL OUTPUT.. Hope you got it,, All there scenarios should come in one place..Book1 is BEFORE... Book2 is AFTER(output)should be...

Please help
 

Attachments

Madhuri_G

New Member
If you could see i have already added OR statement..
=IF(AND(E3<18,AK6<>""),CONCATENATE(D3," ","Family",""),IF(OR(G3="SEGMENT_1",G3="SEGMENT_2",G3="SEGMENT_3",G3="SEGMENT_4",G3="SEGMENT_5",G3="SEGMENT_6",G3="SEGMENT_7",G3="SEGMENT_8"),
IF(F3,"Supporter",
IF(OR(C3="",C3=".",C3="Unknown",LEN(TRIM(C3))=1),
B3&" "&D3,
C3)
),
))
 

Madhuri_G

New Member
Thankyou Bosco!! It worked for my Sample data.. I will try in my original Data on Monday and will let you know.. thanks a ton!:)
 

Madhuri_G

New Member
Hi ,

I'm still getting two initials from the formula, these needs to be get rid off too and replace with 'Supporter'. Can anyone show me some light on this??

A & C
A & P
A & L
A & T
A & Kitsa
A & S
A L
A & C
A & M
A J
A & C
A & G
A & C
A & S
A & C
A & M
A & L
A & G
A & M

These should be masked with 'Supporter'

=IF(AND(AK5<18,AK5<>""),CONCATENATE(H5," ","Family",""),IF(OR(CD5="SEGMENT_1",CD5="SEGMENT_2",CD5="SEGMENT_3",CD5="SEGMENT_4",CD5="SEGMENT_5",CD5="SEGMENT_6",CD5="SEGMENT_7",CD5="SEGMENT_8"),
IF(BJ5,"Supporter",
IF(OR(I5="",ISNUMBER(FIND(".",I5)),I5="Unknown",LEN(TRIM(I5))=1),
E5&" "&H5,
I5)
),
))
 

Madhuri_G

New Member
Hi,

I have encountered a pattern in my excel and I need to change this to 'Supporter'.

The pattern is alphabet and space...
A L - Alphabet and space
B L
C B
D A

which function should i use???

I thought of using FIND function, but there are 26 alphabets to find out...
 

Madhuri_G

New Member
Please check the AFTER & Before formula in Excel, I need FULLSALUTATION formula, it should return "Supporter" if column F is FALSE and has a SEGMENT accociated to it.

For dots(anamolies), one initial, two initials - it must return "Supporter". Any quick help is appreciated guys... Pls
 

Attachments

bosco_yip

Excel Ninja
@bosco_yip, @cyliyu
Any heads-up on this issue guys?
Please reply us as in :

1] Remove formula in "After" sheet H2:H21

2] Manual write down what is your expected result in "After" sheet H2:H21

3] What are the conditions in order to obtain your expected result.

4] re-upload of your file in respect the above questions

Regards
Bosco
 

Madhuri_G

New Member
Hi @bosco_yip

1] I have added Before & After sheet to be in column "H"

2] I have written it and its in Red colour (After sheet) column (26 to 33)

3] For First name (A.M, E.N. , A & N, B & P, B.K ) for all these scenarios output (column H should be "Supporter" ) and for First name Bo, An - it should be as it is..

4] I have attached the file can you please look it
 

Attachments

Haz

Active Member
Formula in F2:
=IF(OR(C2="",C2="unknown",LEN(C2)=1,AND(SUMPRODUCT(IFERROR(FIND({" ","&","."},C2),0))>0,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ",),"&",),".",))<3)),TRUE,FALSE)
 

Madhuri_G

New Member
Thanks @Haz Will try this and let you know,, I think for this scenario if First name is "George." it displayed as Supporter - it should be "George." only because its length is <3
 

Haz

Active Member
You may want AS (in C20) to show as Supporter too, in that case change the formula to this

=IF(OR(C20="",C20="unknown",LEN(C20)=1,AND(OR(SUMPRODUCT(IFERROR(FIND({" ","&","."},C20),0))>0,EXACT(UPPER(C20),C20)),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C20," ",),"&",),".",))<3)),TRUE,FALSE)
 
Top