Help

Re: Combine multiple fields based on certain conditions

Solved
Jump to Solution
167 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Danny_Mohill
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I have a formula that almost works. I'm trying to make my primary field a formula where it looks at "Column A" and looks at "Column B". If a there's a value in "Column A", enter that value as the primary field. If there's a value in "Column B", enter that value in the primary filed. If there's a value in both "Column A" and "Column B", combine those two values and place a "/" between the two fields. The formula I have looks like this:

 
IF(AND({Column A} != BLANK(), {Column B} != BLANK()), CONCATENATE({Column A}, "/", {Column B}),IF(Column A != BLANK(), {Column A}, {Column B}))
 
This does work. However this formula is adding the "/" character to the beginning of "Column A" and at the end of "Column B" when there's only one value in one of the fields.
The results look something like this:
If there's a value in "Column A" but not in "Column B":
12345/
75326/
23457/
 
If there's a value in "Column B" but not in "Column A":
/93664
/30483
/54214
 
I tried replacing
 
IF(Column A != BLANK(), {Column A}, {Column B}))
with this 
IF({Column A} != BLANK(), LEFT({Column A},LEN({Column A})-1), RIGHT({Column B},LEN({Column B}-1)))
 
But that didn't remove the "/" mark in instances where only one value was in either "Column A" or "Column B"
17 Replies 17

I don't get it. Conditions C and D are independent or they can affect each other? What kind of output you expect?
Could you please give an example? No need to expand A,B,E,F, suppose they all present or all absent
If C and D are independent, just copy the same formula with other letters and put & between them.
Possible flaw - is when you see 'all ok' and don't know - 'which all is ok" 🙂    I mean A&B both present or E&F

@Alexey_Gusev I appreciate the pushback, as it helped me rephrase my question. I'm looking to provide multiple notifications for one field; existing or missing: copy (3 fields), image (1 field), and URL (1 field)

IF(
{Creative Placement} = 'Email Seasonal Trends Category 2-Column 840x630 Medium',
IF(AND({Headline/Title Copy (EN)}='',{CTA Copy (EN)}=''), 'Missing Copy: Headline & CTA',
IF(
{Creative Placement} = 'Email Seasonal Trends Category 2-Column 840x630 Medium',
IF(AND({Headline/Title Copy (EN)},{CTA Copy (EN)}=''), 'Missing Copy: CTA',
IF(
{Creative Placement} = 'Email Seasonal Trends Category 2-Column 840x630 Medium',
IF(AND({Headline/Title Copy (EN)}='',{CTA Copy (EN)}), 'Missing Copy: Headline'
)
)
)
)
)
)

AND

IF(
{Creative Placement} = 'Email Seasonal Trends Category 2-Column 840x630 Medium',
IF({English MC Link}='', 'Missing Image'
)

AND

IF(
{Creative Placement} = 'Email Seasonal Trends Category 2-Column 840x630 Medium',
IF({Full URL + Reftag}='', 'Missing URL'
)

Well, I added line breaks and it looks good, I think

Alexey_Gusev_2-1738637712208.png

IF({Creative Placement}='Email Seasonal Trends Category 2-Column 840x630 Medium',
IF({Headline/Title Copy (EN)}, IF({CTA Copy (EN)}='','Missing Copy: CTA'),
  IF({CTA Copy (EN)}='','Missing Copy: Headline & CTA','Missing Copy: Headline')) &
IF({English MC Link}='', '\nMissing Image') & 
IF({Full URL + Reftag}='', '\nMissing URL') )  

@Alexey_Gusev This is next level! Thank you so much!!

@Alexey_Gusev one last question, is there a way to separate the responses with a ',' or ';' vs. a line break?

That's strange, but I never met such simple thing.
Maybe better solution exists. I would add ";" in the end of each text output except last
then remove possible trailing ';' in the end by wrapping whole formula  in a such way:

 

REGEX_REPLACE(
...
whole formula
...

';$','')

 

Of course, line breaks '\n' should be removed

@Alexey_Gusev so this?

REGEX_REPLACE(
IF({Creative Placement}='Email Seasonal Trends Category 2-Column 840x630 Medium',
IF({Headline/Title Copy (EN)}, IF({CTA Copy (EN)}='','Missing Copy: CTA;'),
  IF({CTA Copy (EN)}='','Missing Copy: Headline & CTA;','Missing Copy: Headline;')) &
IF({English MC Link}='', '\nMissing Image;') & 
IF({Full URL + Reftag}='', '\nMissing URL') ) ';$','')

 '\nMissing Image;') &
IF({Full URL + Reftag}='', '\nMissing URL') ) ';$',''