Unique Children

Art Schneiderheinze

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?

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)&gt;1,1,0)

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

Art Schneiderheinze

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.

Art Schneiderheinze

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). 

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

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

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

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

6 CSS 225  UNIQUE?: 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}

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

 

 

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.

Art Schneiderheinze

Any luck testing out your idea?

Paul Newcome

Still working on it. Things got really busy here at work, so I haven't had as much time lately. Sorry about that.

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)