Data Trending

Mike Arbaugh

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

Andrée Starå

Hi @Mike Arbaugh

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!

Mike Arbaugh

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

Paul Newcome

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?

Mike Arbaugh

I want it to happen as soon as a new trend. I want the full rows to move. Thanks

Paul Newcome

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)

Mike Arbaugh

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

Paul Newcome

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.

Mike Arbaugh

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.

Paul Newcome

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.

Mike Arbaugh

Paul

How would I set it up with the columns below? Product will be what changes of name.

Paul Newcome

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.

Mike Arbaugh

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.

Mike Arbaugh

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.

Paul Newcome

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.

Mike Arbaugh

I figured it out. I had to sort the auto number and then it fixed it.