Unique Children

I am looking for a way to determine when a value in a child row is unqiur from values in other children (in the same parent).

EXAMPLE:

I have a sheet with four courses. A course may have children if it is offered in more than one modality (e.g., a row if offered om ground and another row if offered online). If it is only offered in one modality, it is one row, no children

COURSE 1 (SME: John, 1) <-- no children

COURSE 2 <--parent

Modality 1 (SME: Tara, 1) <-- child

Modality 2 (SME: Tara, 0) <-- child

COURSE 3

Modality 1 (SME: Greg, 1)

Modality 2 (SME: Greg, 0)

Modality 3 (SME: Holly, 1)

COURSE 4 (SME: Holly, 1)

What i want to do is have a column that indicates when a SME is unique for a course. If I can determine when a SME is unique for a xourse, I can automate a payment process.

Tara is listed as SME for both modalities of Course 1, but it should only indicate she is the only SME. Whereas, Greg and Holly are unique for Course 3. Each should be paid.

I added a 1 and 0 for each SME which could be the result displayed via a formula. 1 means the SME is unique and should be paid a stipend, either for 1 or 2 modalities for a course. 0 means they are the same SME for a course, so they get 1 stipend.

John gets a stipend as he is the only SME for Course 1. Holly gets two stipends, even though her name appears twice because they are different courses. Greg, like Tara, gets one stipend, even though their names appear twice, but they are the same course. However, Holly gets one too for the same course because she is doing a different modality.

All four courses are different. Each modality within a course is different.

If a course has modalities on subsequent rows, they are children of thr parent row (course). If a course has just one row, it has no children.

Any thoughts?

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    i think this sounds a lot more complicated than it actually boils down to. Keep in mind you have to maintain the sheet, and the names have to be the same every time they are input on the sheet or there will be errors.

    =if(countif(children([Sme column]5),[Sme column]@row)>;1,1,0)

    Where Sme column is the column that contains the name, and [Sme column]5 is the parent of th

  • Thanks. I've used a similar formula to count the overall number of unique SMEs. (If a SME is working on more than one course,, he or she is counted only once.)

    However, this isn't a sustainable way to count unique SMEs within a single course. A SME may be assigned to more than one course (thus, they are counted ONCE for the entire list of courses), but they still get two stipends, because they are working on two different courses.

    In my example, Holly is counted ONCE as a unique SME for all four courses, but she gets TWO stipends since she is assigned to two courses. So, she has a "1" next to both of her names. Greg also appears twice (counted ONCE as a unique SME for all four courses, but only gets ONE stipend. He appears twice in the list, but they are for the same course, just different modalities of the same course.

    This is what I cannot figure out: how to come up with the "0" or "1", as shown next to each name, to indicate a SME is unique within a course.

  • I can use something like in each row:

    =IF(ISBLANK([SME #1 (Name)]@row), "", IF(COUNTIF([SME #1 (Name)]$2:[SME #1 (Name)]@row, [SME #1 (Name)]@row)<> 1, 0, 1))

    However, I would have to manually change the $2 when used with a new course, so it compares the SME names just for the course. So, I could use this in UNIQUE? column for row 2 (CSS 200), but if I use for CSS 205 (rows 3, 4, 5), I'd have to manually change $2 to $3, so the comparison of SMEs is only among the rows for that one course. And do the same for CSS 225 (rows 6 to 9), changing $2 to $6.

    BL = Blended, OL = Online, OG = On-ground, SME = Subject Matter Expert

    John should get a stipend for CSS 200 (unique?=1). Tara should get one stipend for CSS 205 (even though she is doing two modalities, BL and OL). Greg should get one stipend for CSS 225 (even though he is doing two modalities, BL and OL). Notice Unique? = 1 for row 7, but =0 for row 8, since him being listed a second time is not unique for the course. Holly should also get a stipend for CSS 225 (for one modality, OG). Holly gets another stipend for CSS 250 (for the one modality, BL, she is assigned to).

    2CSS 200BL SME: John UNIQUE?: 1 {<--not a parent or child}

    3CSS 205UNIQUE?: N/A {<--parent}

    4 CSS 205 BL SME: Tara UNIQUE?: 1 {<--child}

    5 CSS 205 OL SME: Tara UNIQUE?: 0 {<--child}

    6CSS 225UNIQUE?: N/A {<--parent}

    7 CSS 225 BL SME: Greg UNIQUE?: 1 {<--child}

    8 CSS 225 OL SME: Greg UNIQUE?: 0 {<--child}

    9 CSS 225 OG SME: Holly UNIQUE?: 1 {<--child}

    10CSS 250BL SME: Holly UNIQUE?: 1 {<--not a parent or child}

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I haven't tested yet, and the idea is still really fresh, but...

    What about adding in a helper column using the PARENT function to replicate the corresponding course name for each row. You could then use a COUNTIFS instead of a COUNTIF to include the new criteria of the course name being the same.

    Or even a JOIN(PARENT([Course Column Name]@row:[SME Name]@row)then counting those unique values. They would both provide the same results.

    I will do some testing to try to get you a more specific solution unless you are able to get it to work before I am able to get around to it.

  • Any luck testing out your idea?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    还在研究它。事情在这里很忙t work, so I haven't had as much time lately. Sorry about that.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sorry for the delay. I was spending too much time on this and overthinking it. I took a break for a little while, came back to it, and had a "Duh! Moment".

    .

    So here are our columns:

    Unique?(checkbox)

    Helper(text/number)

    Course Name(text/number)

    SME(text/number)

    .

    Here are our formulas that go into row 1 and can be dragfilled on down:

    [Unique?]1:=IF(ISTEXT(Helper@row), IF(COUNTIFS(Helper$1:Helper@row, Helper@row) = 1, 1))

    Helper1: =IF(IF(COUNT(CHILDREN([Course Name]@row))= 0, COUNT(ANCESTORS([Course Name]@row)), "") = 0, LOWER([Course Name]@row), IF(IF(COUNT(CHILDREN([Course Name]@row))= 0, COUNT(ANCESTORS([Course Name]@row)), "") > 0, LOWER(PARENT([Course Name]@row)), "")) + LOWER(SME@row)

    Comm.PNG

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/47186/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/47186/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions