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?

Find more posts tagged with

Sheets

Formulas

Comments

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

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)

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…