Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Combine 2 or 3 cells into one?

Angie Hatfield
Angie Hatfield ✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

I have a webform that collects data for a request, Department, Type, Etc. These are separate columns. I would like to somehow filter the some of the information received into one column, which would then be a code for that particular request. The code is then used in other locations as an identifier.

 

Is there any way to do this? I looked at formulas but didn't really see anything that applied.

 

Thank you

Comments

  • Kennedy Stomps
    Kennedy Stomps Employee
    edited 05/25/16

    Hi Angie! Though I don't know exactly what the sheet you're working with looks like, I can think of a way that you might be able to accomplish this using an IF formula and concatenation. 

     

    For example, say every time a request is submitted for the Printing department, I want the code to begin with P and every time a request is submitted for our Design department, I want it to begin with D. I could use an IF formula to start building the code based on which department was selected in the "Department column":

     

    =IF(Department1 = "Printing", "P", IF(Department1 = "Design", "D"))

     

    Then, I can add additional IF statements to continue building the code based on the value in the Type column. In this case, imagine we code new job requests ("New Job") with "001" and ongoing projects ("Recurring") with 002:

     

    =IF(Department1 = "Printing", "P", IF(Department1 = "Design", "D")) + IF(Type1 = "New Job", "001", IF(Type1 = "Recurring", "002"))

     

    Alternatively, if you just want to collect the main details from the request into one column, you can simply concatenate the values in another column:

     

    =Department1 + ", " + Type1 

     

    This will return the value in the Department cell referenced, then a comma and a space, and finally the value from the Type cell referenced.

     

    Let me know how this works for you.

     

     

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭
    edited 05/25/16

    Hi Kennedy,

     

    Using IF statements look ideal for my needs. I have played around with it and keep getting #Unparseable. I've tried to figure out what I've got wrong but am getting nowhere.

     

    Here's my formula:

     

    =IF(Department = "1 General", "1", IF(Department = "2 Executive", "2")) + =IF(Document Type = "Agent", "AG", IF(Document Type = "Claim", "CL"))

     

    Any suggestions?

     

    thank you for your assistance!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Angie,

     

    You have too many "=" and need both a column and row reference

     

    Document as column name

    23 as row

     

    Document23

     

    For columns with spaces, use "[]"

     

    [Document Type]23 not Document Type23

     

    So

     

    =IF(Department23 = "1 General", "1", IF(Department23 = "2 Executive", "2")) + IF([Document Type]23 = "Agent", "AG", IF([Document Type]23 = "Claim", "CL"))

     

    You should also likely account for blanks or unrecognized entries, perhaps like this:

     

    =IF(Department23 = "1 General", "1", IF(Department23 = "2 Executive", "2", "D")) + IF([Document Type]23 = "Agent", "AG", IF([Document Type]23 = "Claim", "CL", "DT"))

     

    where a missing/unrecognized Department will show up as D and a missing/unrecognized Document Type will show up as DT

     

    I hope this helps.


    Craig

     

     

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭

    Craig,

     

    That absolutely does work, thank you very much!

    Obviously I am new to IF statements, but I think I am going to become very good at them. I see a lot of potential to make vast improvements to my sheets.

     

    Thank you again, I appreciate your help!

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭
    edited 05/25/16

    What is the proper way to add a third and maybe a fourth column to this equation?

     

    Now i want to combine Department, Document Type, I/O (Incoming or Outgoing, and module.

     

    I messed around trying to get it to work, but I keep getting #unparseable.

     

    I'm not sure how the third (and fourth) should be displayed. I've tried removing one or two of the end brackets, I've tried renaming my column from I/O to I O, amongst other things.

     

    =IF(Department21 = "1 General", "1", IF(Department21 = "9 Operations", "9")) + IF([Document Type]21 = "Contract", "CO", IF([Document Type]21 = "Claim", "CL")) + IF(I/O21 = "Incoming", "I", IF(I/O21 = "Outgoing", "O", IF(I/O21 = "Both", "B")))

     

    I'm getting frustrated with myself for not being able to figure this out or locate the answer!

     

    thank you very much!

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭

    I figured it out. I had to change I/O to IO and I have the correct amount of brackets.

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭
    edited 05/26/16

    I just want to say WOW, this is one of the most powerful advantages over using Excel. I can now have a code generated based on information submitted on a web form and requires no work on my part. The codes are perfectly standardized to our needs and follow coding used in other parts of our Company. 

This discussion has been closed.