Get values from a column with blanks insert into another column without blanks

Options

I have a some projects on a sheet that all follow the same template. The ProjName column has the project name (duh) and is the only value in that column for each project.

ProjName - Task - Date - Comment

project 1 - - - -

  • First task - 1/2/2021 - comment1
  • second Task - 2/1/2021 - comment2

project 3

  • First task - 1/2/2021 - comment1
  • second Task - 2/1/2021 - comment2

Project 3

... and so on ...

On another page I want to grab the project names and add them in a different column without the blank cells between them that hold the values for the taska and other stuff so that I end up with a sheet that looks like this:


project 1

project 2

Project 3

Ho can I get the contents of the column, strip away the nulls, and then add them to the colume as shown above?

Best Answer

  • Freymish
    Freymish ✭✭
    Answer ✓
    Options

    Here's how I ended up solving this:

    I created a column to store a lookup from the source page and just replicated the results into each row so I can use the @row method:

    ="~" + JOIN(DISTINCT({Master Projects Range 2}), "~") + "~"

    I created another helper column to tell me the number of the row I'm on, and then two more (PNameLen & PNameLen2) to hold values for the stating and ending positions of the substring.

    Next, to figure out where the start and end of the substring is in the ~delimited string created above, starting from after the initial ~ I count the number of characters in the string up to the next ~ and that's where the string ends.

    =IF(COUNT(index1:index@row) <= 2, FIND("~", PNames@row, PNameLen1), FIND("~", PNames@row, PNameLen1 + 1))

    In the next row I pull the ending from the previous PNameLen2 and use it +2 as the starting point of the next substring. Rinse repet to the end of the list.


    Then in the ProjectName column I use the following to show the name of the project:

    =IF(NOT([PNameLen2]@row = ""), MID(PNames@row, PNameLen@row + 1, [PNameLen2]@row))

    And that is what I needed to do. :)

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Freymish

    I hope you're well and safe!

    • You could maybe use the Cell-linking feature if no new rows are added.
    • You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Freymish
    Freymish ✭✭
    edited 03/16/22
    Options

    Thank you for your response. Cell linking works but I was hoping for something more automatic. As this is for a project summary report we want to be sure that all the projects generated are represented and that nothing gets missed because someone for got to do something.

    I suspect the second method is the way to go and I had started down that road but I was hopong for some advice on how to achieve that with the functions available.Essentially, I want to create an array and loop through it putting a value in each cell of the column. The other cells in that row will use that value in a VLOOKUP function to pull specific cells from the project relative to the project name on the source sheet..

  • Freymish
    Options

    I have an idea of how to do this. Using the following formula I can extract the values I want:

    =JOIN(DISTINCT({Master Projects Range 2}), "~")

    Which gives me this:

    project 1~project 2~Project 3

    So the question now becomes how do I grab value 1 or two from the list? I don't know what these values will be so it needs to be similiar an array position like arrayIndex[0]

    Getting closer...

  • Freymish
    Freymish ✭✭
    Answer ✓
    Options

    Here's how I ended up solving this:

    I created a column to store a lookup from the source page and just replicated the results into each row so I can use the @row method:

    ="~" + JOIN(DISTINCT({Master Projects Range 2}), "~") + "~"

    I created another helper column to tell me the number of the row I'm on, and then two more (PNameLen & PNameLen2) to hold values for the stating and ending positions of the substring.

    Next, to figure out where the start and end of the substring is in the ~delimited string created above, starting from after the initial ~ I count the number of characters in the string up to the next ~ and that's where the string ends.

    =IF(COUNT(index1:index@row) <= 2, FIND("~", PNames@row, PNameLen1), FIND("~", PNames@row, PNameLen1 + 1))

    In the next row I pull the ending from the previous PNameLen2 and use it +2 as the starting point of the next substring. Rinse repet to the end of the list.


    Then in the ProjectName column I use the following to show the name of the project:

    =IF(NOT([PNameLen2]@row = ""), MID(PNames@row, PNameLen@row + 1, [PNameLen2]@row))

    And that is what I needed to do. :)