Get values from a column with blanks insert into another column without blanks
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
-
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
-
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.
-
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..
-
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...
-
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. :)
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives