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?
-
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)
-
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.
-
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.
-
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.
-
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.
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!