Sort data conditionally from one sheet to another

lmiller

Hello,

I am looking to take the rows from one sheet that have "x" in column 15 and output all of them in another sheet. the closest that I've gotten is:

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 1)

Which when placed in row 1 column 1 on Page 2, pulls the first matching result in row 1.

I have each column like so:

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 1)

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 2)

=INDEX({Page 1 Range 1}, MATCH("X", {Page 2 Range 2}, 0), 3)

etc...

in order to pull a full row into another page.

However, this only works for the first match for "X". Trying this in a different row outputs the same data.

I'm sure that there is a better way to do this. In excel, I would have used the FILTER function, but in Smartsheet that isn't an option. Any advice?

Jeff Reisman

There are a few ways to do this.

If this is a one-time thing, like you just want to extract those rows with X in column 15, then I would recommend creating an automation rule, set it to trigger at a certain time, to copy all rows with X in column 15 to another sheet.

If this needs to be a regular thing, add a helper checkbox column to your sheet, call it "Moved?". Create your automation rule with trigger as "When rows are added or changed", when Column 15 changes to X. Add a condition block, where 'Moved?' column is unchecked. Set the Action to copy the row to the other sheet, and add another action to change the Moved? column to checked.

Jeff Reisman

There are a few ways to do this.

If this is a one-time thing, like you just want to extract those rows with X in column 15, then I would recommend creating an automation rule, set it to trigger at a certain time, to copy all rows with X in column 15 to another sheet.

If this needs to be a regular thing, add a helper checkbox column to your sheet, call it "Moved?". Create your automation rule with trigger as "When rows are added or changed", when Column 15 changes to X. Add a condition block, where 'Moved?' column is unchecked. Set the Action to copy the row to the other sheet, and add another action to change the Moved? column to checked.

lmiller

https://community.smartsheet.com/discussion/comment/316532#Comment_316532

That sounds great!

I attempted to do this by adding the trigger "When rows are changed" "when any field changes" and to run when triggered.

The condition was "Where Column is equal to x" with no otherwise selected

And the action was to move the rows to the second sheet.

This does not work for some reason. Any ideas why?