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

Freymish

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?

Freymish

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. :)

Andrée Starå

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!

Freymish

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

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

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. :)