• 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

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)
),
))
 
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.
 
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

  • Book1.xlsx
    11 KB · Views: 15
  • Book2.xlsx
    12 KB · Views: 5
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)
),
))
 
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!:)
 
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)
),
))
 
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...
 
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

  • Book1 - Copy.xlsx
    18.5 KB · Views: 4
@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
 
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

  • before_after.xlsx
    22.5 KB · Views: 7
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)
 
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
 
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)
 
Back
Top