How to get all rows with 1st column starting with specific alphabets

Himanshu Agarwal

Dataset:

Required Output:

I am trying to extract data from the rows which have its first column starting with particular contents. As I have shown in the attached example, I wish to get data of columns A, C, and D if the column A starts with 'as'.

Please help me to achieve this. Thanks!

Dataset.png

Required Output.png

Connor Hartford

Hi Himanshu,

When you say you want to "extract" the data, are you putting it on another sheet with a formula or just want to view a filtered version? If all you want to do is display the data with 'as', you can use a filter on the sheet, or you can use a report to filter. I think you should look into a report if you just want to display the table you show.

If that's not what you want, then I can help with the formula on another sheet, just let me know.

Here's a Smartsheet help article for doing so: https://help.smartsheet.com/articles/522214-creating-reports

Connor

Himanshu Agarwal

Hi Conor, thank you for your response. I am putting it on another sheet with a formula. Reports would also not help me as I have to work on the extracted data on the second sheet.

What I'm looking for is a formula to get the required data on another sheet. If you could help me with that that would be great! Thanks, again!

Andrée Starå

@Himanshu Agarwal

Hi Himanshu,

Is the first column a running number and unique (Row ID)?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Himanshu Agarwal

Hi Andrée,

I am afraid no, there is no running number or unique row ID available in the dataset. The first row and column are just to indicate the row # and column #.

Let me know if you would like to know more.

Regards,

Himanshu

Etienne Mermillod

You can add a column on the sheet with the given formula:

=IF(LEFT(mycolumm, 2) = "as", 1, 0)

If mycolumm cell starts with "as" then it will result into 1 else 0. Then create a filter based on the column and the report.

Himanshu Agarwal

Hi Etienne,

Thank you for your response. This will surely give me rows with first column starting with 'as' however in the final report, I do not want the blank rows. It is a high-level sheet that Director and VPs see & perform actions in real time. I hope you understand where I am facing the real challenge.

Regards,

Himanshu

Ryan Kramer

@Himanshu Agarwal ,

I assume the data set you are showing is simply some mock data for showing the problem. I would recommend utilizing the API. You would be able to extract the grid's rows and then apply basic programming regular expression with if then statement, if it matches this, add it to here, etc. So if matches the first 3 characters of this regex, add it to sheet X.

So in the way your leadership uses it, it will be seamless and from a maintenance perspective, far better.

Ryan

Himanshu Agarwal

Hi Ryan,

Thanks for your response. Getting this result from a formula is my first priority. However, if I wouldn't be able to find a formula then I guess I would be left with creating an API or doing it manually. And yes, it is a mock data.

Regards,

Himanshu

Ryan Kramer

@Himanshu Agarwal ,

If you wanted to do it through formulas then you would likely do something like this -

Add X number of helper columns, each with their own regex or starting fields. Each field would have a checkbox next to it. Then you would add formula on the checkbox to validate the given reference Column (A in your case) to see whether the helper column matches. If so, then check yes.

Next you would build a workflow to look for cases where a helper column equaled yes. If so, then move the entry to respective grid.

Then you could scale this for your additional cases and the respective grids.

Ryan

Himanshu Agarwal

Hi Ryan,

Thanks again for your response. Yes, this would work, however it is not possible for me to add an extra column in the original dataset. I wish it were possible.

Sorry for proving to be so rigid. I have searched for a formula for this problem for so long that I can't remember. I have required this formula in most of my reports across different platforms (Excel/ Google Sheets/ Smartsheets...). I would feel so lucky when I'll finally find a solution to my problem. Thanks, though.

Regards,

Himanshu

Ryan Kramer

@Himanshu Agarwal ,

If you can't modify the base sheet, why not copy it and then create a workflow off that?

Ryan

Himanshu Agarwal

Hi @Ryan Kramer,

Thanks for the follow-up response. This is not possible because the dataset is dynamic and we need a real-time update. If I'll create a copy of the entire dataset and then pull relevant data from that copy, smartsheet takes several minutes to reflect the data updated in the main dataset. I did try this once about an year ago. I was sitting duck in front of my manager waiting for the changes to reflect in the third sheet. The sheet did not update until I came back to my seat. I am sorry, I guess.

Sheet/ Grid 1: Main Dataset

Sheet/ Grid 2: Another copy of the main dataset

Sheet/ Grid 3: Required output

Regards,

@Himanshu Agarwal

Ryan Kramer

@Himanshu Agarwal ,

Yeah. Those situations suck!

Well, you be painted into a corner then. Once again, go with the API. I have built some pretty big processes that use the API and that's pretty much what I lean on when you get into the 100s of users and need to be able to guarantee a level of performance.

Ryan

Himanshu Agarwal

Hi @Ryan Kramer,

I guess so. Thanks much for your responses.

Regards,

@Himanshu Agarwal

Andrée Starå

@Himanshu Agarwal

I saw that it got answered already!

Let me know if I can help with anything else!

Best, 

Andrée