Data Trending
I am working on a project sheet. I have a cell that is made up with data that is submitted by a form. The form has around 30 different drop down items. You can only select one item from the dropdown list at a time. Here is the issue I have.
In cell "A", I might have the same text "You" listed 20 times. Then it will switch over to "Your", then this will start the next trend of data. I want to be able to move all of the data that shows "You" to another sheet as soon as I start seeing the "Your" data. Then later as I see the next trend of data "Yes", I want the "Your" data to move. This is just an example. Is it possible to do this. Thanks
Answers
-
I hope you're well and safe!
Yes, it's possible.
Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to 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, Awesome, 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.
-
Here you go. I will have runs of the same data "Test 1" for a few submittal's. Then the data will switch to a different name "Test 2". When the switch happens, I need all of the "Test 1" rows go to a different sheet. I need this process to repeat daily. I could have around 50 different changes in a 24-hour cycle. Thanks
-
Do you want it to happen daily, or do you want it to happen as soon as a new "trend" is started? Would all of the moved rows be going to the same sheet, or do you need separate sheets for each trend?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I want it to happen as soon as a new trend. I want the full rows to move. Thanks
-
Ok. In that case you would insert an auto-number column (called "Auto" in this example) with no special formatting.
Then you would insert a flag type column with this column formula:
=IF(COUNT(DISTINCT(COLLECT([Column2]:[Column2], Auto:Auto, @cell<= Auto@row)))> 1, 1)And then you would set up a move row automation to run off of this flag becoming flagged.
Edited to update formula:
=IF(AND(COUNT(DISTINCT([Column2]:[Column2]))> 1, COUNT(DISTINCT(COLLECT([Column2]:[Column2], Auto:Auto, @cell<= Auto@row))) = 1), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul
I have been trying to get something to work here. Here is what I have going on. I have a form that is being filled out. They will select the product they are running "Test 1" below. They could have 10 to 20 of the same product before switching to a different product "Test 2". When they switch, I no longer want to trend the data from the product that is "Test 1". I now need to trend "Test 2" data. This data is entered in every 30 minutes and the product type might change every few hours. I tried to do a match of some kind and some helpers and I couldnt figure it out. I also tried to have a work flow move data, but then all my formulas move. Any help with this would be great. Thanks
-
You would use the formula above to flag the previous trend and then use a Move Row automation to move all flagged rows to another sheet.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I don't need to move them if I don't have to. If I can have a check box or formula that would change a helper column to read the same number. I just need some way to have my report look at the most recent data without me having to change the report. The data will change several times a day.
-
Without moving the row, you will need a double column setup. In one you would have the formula
=COUNT(DISTINCT(COLLECT([Column2]:[Column2], Auto:Auto, @cell<= Auto@row)))
Then in the flag column you would use
=IF([Helper Column]@row = MAX([Helper Column]:[Helper Column]), 1)
This will flag the most recent.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul
How would I set it up with the columns below? Product will be what changes of name.
-
You will still have the auto number column called "Auto". Then you would have a column with the first formula in it and finally the flag column with the second formula in it. The flags should be on the most recent Product.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul
You are the MASTER. I have been working with smartsheets for two weeks and no one could figure this out. I have five different people look at this. The formula you gave me works great and I can now finish my project.
-
One more question Paul. It is working half way. When I have two product names that are close, it is picking it up as the same name and counting it. Can I add something into the formula to have exact match.
-
Are you able to provide a screenshot of the two that are similar that are counting as the same? It shouldn't be doing that.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I figured it out. I had to sort the auto number and then it fixed it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!