*Need Assistance* for Complicated IF Formula

G

GraffikPleasure

New member
Joined
Sep 16, 2016
Messages
6
Reaction score
0
Points
0
Ok, so I'm doing mail merges for html email templates. I have these things so close to being 100% but I just need to do the following:
I need to insert editors and their affiliation like this: John doe (USA), Jane doe (Africa), and Frank doe (Canada)

How can I set myself up in Excel to add the parenthesis IF there is a second or third editor. And how can I make the "and" be inserted when there is only two or three editors where it needs to go?

Attached I have the file setup I'm using. View attachment TEST.xlsx

THANK YOU
 
is there always a max of 9 persons?
are there blank columns when number of persons is less than 9?
 
NBVC, you are correct, there is always a max of 9 people, and when there are only say 3 people, the columns for 4 and on are indeed blank.

thanks for taking interest!
 
Try:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(S2,"(",AB2,"), ",T2,"(",AC2,"), ",U2,"(",AD2,"), ",V2,"(",AE2,"), ",W2,"(",AF2,"), ",X2,"(",AG2,"), ",Y2,"(",AH2,"), ",Z2,"(",AI2,"), ",AA2,"(",AJ2,"), ")),"(),",""),","," and",COUNTA(S2:AA2)-1),",","",COUNTA(S2:AA2)-1)

copied down.
 
Hey NBCV, your formula works and your timing is amazing on how fast you turned this around, I really appreciate it!

The only problem is, is I'm getting #VALUE! when I only have 1 person and affiliation.


Thank you for your time and quick turnaround!!!
 

Attachments

  • TEST-Capture.JPG
    TEST-Capture.JPG
    89.6 KB · Views: 13
Hey,

Actually I was wrong there. Attached you will see what is going on.
I added some names to the document and I'm getting mixed results now.

Thanks
 

Attachments

  • TEST_2.xlsx
    11.3 KB · Views: 13
I didn't know you were going to have commas in the fields already... I need to make adjustments to compensate...

it may be a while.. I am a little busy atm.
 
Ok... I am sneaking this in....

Does this do it?

=IF(COUNTA(S2:AA2)=0,"",IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(S2," (",AB2,")^^ ",T2," (", AC2,")^^ ",U2," (",AD2,")^^ ",V2," (",AE2,")^^ ",W2," (",AF2,")^^ ",X2," (",AG2,")^^ ",Y2," (",AH2,")^^ ",Z2," (",AI2,")^^ ",AA2," (",AJ2,")^^ ")),"()^^",""),"^^"," and",COUNTA(S2:AA2)-1),"^^","",COUNTA(S2:AA2)-1),"^^",", "),CONCATENATE(S2,"(",AB2,")")))

copied down
 
Hey, it works except only when there is just one person, it does not put a space between the person and their affiliation.
See the 2nd line below.

Person 1 (Affiliation 1), Person 2 (Affiliation 2), Person 3 (Affiliation 3), Person 4 (Affiliation 4) and Person 5 (Affiliation 5)
Chen Wang(FAC, USA)
Karen Ras (University of GGG, USA), Pam Norp (University of GGG, USA) and Robin Colon (University of GGG, USA)


*BUT, I AM GOOD! --- So I added a space before anytime you mentioned (",AB2,") - before the first parenthesis... and then in the next column I did the TRIM function to remove multiple spaces and VOILA, its PERFECT!!!

I appreciate your timeliness and I am thoroughly satisfied!

Thanks!
 

Attachments

  • TEST-Capture2.jpg
    TEST-Capture2.jpg
    95.3 KB · Views: 13
You don't need to add another column, you can put the TRIM in the same formula:

=IF(COUNTA(S2:AA2)=0,"",IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(S2," (",AB2,")^^ ",T2," (", AC2,")^^ ",U2," (",AD2,")^^ ",V2," (",AE2,")^^ ",W2," (",AF2,")^^ ",X2," (",AG2,")^^ ",Y2," (",AH2,")^^ ",Z2," (",AI2,")^^ ",AA2," (",AJ2,")^^ ")),"()^^",""),"^^"," and",COUNTA(S2:AA2)-1),"^^","",COUNTA(S2:AA2)-1),"^^",", "),TRIM(CONCATENATE(S2," (",AB2,")"))))
 
Yup, you are a genius at work.

You've inspired me to take Excel a little more serious now. Excel has now taken my HTML templates that would take literally hours (day and a half in some cases) and now they take less than 30 minutes start to finish.

Do you recommend any sites that are good self teaching sites for Excel?

Thanks!
 
There are many sites that give some tutorials. Other that give tips. Others that focus on VBA codes and Excel advanced formulas. There are many blogs on all aspects of Excel from Formulas to VBA, pivot tables, powerpivot and powerquery... It's hard to gather them all....

Some of the one's I have bookmarked are:

http://www.jegsworks.com/Lessons/intro-numbers.htm

http://www.datapigtechnologies.com/ExcelMain.htm

http://www.andypope.info/

http://www.contextures.com/

http://www.xl-central.com/

Those should be a good start...
 
You must log in or register to reply here.
Back
Top