How best to copy partial information from one column into another?

David Brandt

I am using IF(ISBlank) and IF(Contains) together to automatically populate a a cell in one column from data in an adjacent cell/column, but the formula won't let me add any more combinations of the formula to complete this task for about 80 pieces of data. (The formula tops out at 61 in this scenario and won't let me add any more keystrokes.) I'm sure there's a cleaner, quicker solution, but I'm not a Smartsheet guru.

The scenario is:

Column A contains a contact list of ~600 names associated with their company (~80 companies) in this format: Last Name, First Name - Company Name. It is in a multi-select drop-down column. When a user selects a contact (or multiples), I want the Company Name to automatically populate into the adjacent cell in Column B.

This formula works:

=IF((ISBLANK([COLUMN A]@row)), "None", (IF(CONTAINS("Company Name",[COLUMN A]@row, "Company Name", "") +IF(CONTAINS(

... but, like I noted, Smartsheet can only handle about 60 of the 80 names referenced in the repeating formula.

Stumped. What's the better way?

Genevieve P.

Hi @David Brandt

I'd be interested to know if there's a more succinct way to do this as well, since your approach is how I would build this out.

It sounds like you may be hitting cell character limits. What about splitting the formula into the First 40 and then adding another column to return the Second 40, and in a third column add together the two outputs?

Cheers,

Genevieve

David Brandt

Hi @Genevieve P. - Thanks. That seems like a workable option, though hopefully others may have some thoughts on a more succinct option for us. Best - David

Genevieve P.

Hi @David Brandt

The great thing is that after you've created the two longer formulas, combining them into the new column will be very easy!

Ex:

=[Column 1]@row + " " + [Column 2]@row

In regards to splitting up the company names, what about having the columns be Multi-Select and then use CHAR(10) after each name? This would break each name out into a separate value.

I would actually say since your final column will be adding the two cells together, if none of the company names are shows then we can put that statement in your final formula instead of into the two halves.

Ex:

=IF(AND([Formula 1]@row = "", [Formula 2]@row = ""), "None", [Formula 1]@row + CHAR(10) + [Formula 2]@row

So then your two halves would go right into your IF(CONTAINS statements:

=IF(CONTAINS("Company One", [COLUMN A]@row), "Company One" + CHAR(10)) + IF(CONTAINS("Company Two", [COLUMN A]@row), "Company Two" + CHAR(10)) + IF(CONTAINS("Company Three", [COLUMN A]@row), "Company Three" + CHAR(10)) + IF(CONTAINS("Company Four", [COLUMN A]@row), "Company Four" + CHAR(10)) + IF(CONTAINS("Company Five", [COLUMN A]@row), "Company Five" + CHAR(10))

... etc

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

Genevieve P.

Hi @David Brandt

My apologies for the delay!

The 0's are really interesting, it may have to do with the fact that we haven't told the IF statements what to do if it doesn't find that value.

Try adding , "" to the end of each IF so that it returns Blank. Also, you don't need the extra ( ) around your IF statements.

Try:

=IF(ISBLANK(ANALYSTS@row), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10), "") + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10), "") + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10), "") + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10), "")))

Genevieve P.

Hi @David Brandt

I'd be interested to know if there's a more succinct way to do this as well, since your approach is how I would build this out.

It sounds like you may be hitting cell character limits. What about splitting the formula into the First 40 and then adding another column to return the Second 40, and in a third column add together the two outputs?

Cheers,

Genevieve

David Brandt

Hi @Genevieve P. - Thanks. That seems like a workable option, though hopefully others may have some thoughts on a more succinct option for us. Best - David

David Brandt

Hi @Genevieve P. - Haven't seen any other options offered so I'm going to handle the formula in the manner you suggest... splitting the formula into 2 columns. Do you know how you would then combine the outputs into a singular column?

Also, in the cells where the result is multiple company names, how would I either include a semicolon or some other divider between each company name? (I think I can just include that after each company name in the formula, but then I also want to eliminate that divider when only one company name populates into the cell. Any ideas on that one?

All the best,

David

Genevieve P.

Hi @David Brandt

The great thing is that after you've created the two longer formulas, combining them into the new column will be very easy!

Ex:

=[Column 1]@row + " " + [Column 2]@row

In regards to splitting up the company names, what about having the columns be Multi-Select and then use CHAR(10) after each name? This would break each name out into a separate value.

I would actually say since your final column will be adding the two cells together, if none of the company names are shows then we can put that statement in your final formula instead of into the two halves.

Ex:

=IF(AND([Formula 1]@row = "", [Formula 2]@row = ""), "None", [Formula 1]@row + CHAR(10) + [Formula 2]@row

So then your two halves would go right into your IF(CONTAINS statements:

=IF(CONTAINS("Company One", [COLUMN A]@row), "Company One" + CHAR(10)) + IF(CONTAINS("Company Two", [COLUMN A]@row), "Company Two" + CHAR(10)) + IF(CONTAINS("Company Three", [COLUMN A]@row), "Company Three" + CHAR(10)) + IF(CONTAINS("Company Four", [COLUMN A]@row), "Company Four" + CHAR(10)) + IF(CONTAINS("Company Five", [COLUMN A]@row), "Company Five" + CHAR(10))

... etc

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

David Brandt

Hi there @Genevieve P. Thumbs up! Thanks for coming back on this. CHAR (10) is a great solution to the divider... much cleaner than how I was approaching! I'll work through your formulas tomorrow, but can see how this will solve my conundrum. Thank you!

Genevieve P.

I'm glad I could help! 🙂

David Brandt

Hi @Genevieve P. Just wanted to circle back with an interesting hiccup. The formulas are doing what they are suppose to but there is some weirdness in the results that I can't figure out. Maybe you've come across this: When there isn't a company name listed in both Formula columns the numeral zero appears in the column without a company listed. And then that zero is populated into the combined column.

There are also instances where a zero will appear in front of the company name "0company e" for instance instead of "company e" in one of the Formula columns (and of course that gets pulled over to the combined column as well). This seems to have something to do with certain combinations of company names, or where they sit within the formula. It is not random, but I can't establish an exact pattern.

FIRM1 Column...

=IF((ISBLANK(ANALYSTS@row)), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10)) + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10)) + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10)) + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10))))

FIRM2 Column...

=IF((ISBLANK(ANALYSTS@row)), "None", (IF(CONTAINS("company e", ANALYSTS@row), "company e" + CHAR(10)) + IF(CONTAINS("company f", ANALYSTS@row), "company f" + CHAR(10)) + IF(CONTAINS("company g", ANALYSTS@row), "company g" + CHAR(10)) + IF(CONTAINS("company h", ANALYSTS@row), "company h" + CHAR(10))))

Weird?

Genevieve P.

Hi @David Brandt

My apologies for the delay!

The 0's are really interesting, it may have to do with the fact that we haven't told the IF statements what to do if it doesn't find that value.

Try adding , "" to the end of each IF so that it returns Blank. Also, you don't need the extra ( ) around your IF statements.

Try:

=IF(ISBLANK(ANALYSTS@row), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10), "") + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10), "") + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10), "") + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10), "")))

David Brandt

Hi there @Genevieve P.

Thanks for the reply. Yes, Smartsheet's little quirks and needs take some getting use to. I came across one of your other exchanges and found / tried that fix , "". And it worked just as you indicated in that comment as well. Thank you very much.