This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Sorry, Community, but I still struggle with the syntax of this tool and could use some help.
I've attached a sample of my data set. The two tabs show the Original State of the data and the Desired State of the data. While I'm actually pulling this from SQL, the same logic will apply to this simplified spreadsheet.
In words, the logic would read something like, "If the Status = "Deleted", then the course_id should inherit the value of the last course_id row above that has a Status = "Active." If the Status is already "Active" the row should remain unchanged.
The sample file is color-coded to show some of the possible combinations. In each example, you'll notice that the course_name fields are identical strings.
Appreciate any help you might offer! Once I get this tool fully mastered, I hope to attack RegEx. We'll see how much my brain can handle.
Hm, since it's not guaranteed that the value you're looking for will always be directly above (as in the biology class in the last 3 rows of your example), this will be a bit tricky using the Multi-Row tool. *(see edit)* I'd probably approach it using something like the workflow below.
Since it appears the course name is your "unique ID" for each course, I've used a Summarize tool, grouping on Course Name and Status, and taking the last course_id for each of those groups.
I then filter to only take the active ones, and join the data back into the main set on Course Name.
Then, using a regular formula tool, you can set course_id = Last_course_id if Status=Deleted.
Let me know if there's any problems with this, or you need clarification,
EDIT: Ah, yes I've completely forgotten that the prior cells would have been evaluated already and replaced if necessary, and so checking the one above it should suffice. My solution still accomplishes the goal, but the others are still correct and more concise
tcroberts -- Also appreciate the logic. I think I am good because of the way I had already sorted the data. I also SHOULD have known that this is a linear tool, starting at the top and progressing through all the rows.