Formula to always reference the parent of a child row within a different column?

Ryan Nix

Hello,

I am trying using the parent/child relationship to group a set of data by month and am using the the formula to rank the children rows by their value:

=IFERROR(RANKEQ(Value@row, CHILDREN(Value$105), 0), "")

But I was wondering if there is a way to make this as a column formula? I would like it to always rank the value of the children but need to manually select the parent row.

Paul Newcome

Assuming your parent rows are unique, my suggestion would be a helper column that replicates the parent row on all of the child rows.

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, PARENT([Primary Column]@row))

Then you can work in a COLLECT function for your range.

=IFERROR(RANKEQ(Value@row, COLLECT(Value:Value, [Parent Helper]:[Parent Helper], [Parent Helper]@row), 0), "")

Paul Newcome

Are you able to provide a screenshot that has data manually entered to show what you are trying to accomplish?

Ryan Nix

Here's a quick snippet

Paul Newcome

Is the blue row row 105 in this instance? Where exactly are you putting this formula? In the white rows of the screenshot?

Ryan Nix

Yes, the blue is the parent row, similar to row 105. The white Primary Column and the Value columns are a copy and paste from a separate source. I then I have a report that will take the top 10 ranked items for each month so we can see what are all the major items within a given month.

Paul Newcome

Assuming your parent rows are unique, my suggestion would be a helper column that replicates the parent row on all of the child rows.

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, PARENT([Primary Column]@row))

Then you can work in a COLLECT function for your range.

=IFERROR(RANKEQ(Value@row, COLLECT(Value:Value, [Parent Helper]:[Parent Helper], [Parent Helper]@row), 0), "")

Ryan Nix

Looks like that might work! I'll need to tweak the parent rows a bit but I think this might do the trick. Thank you!

Paul Newcome

Happy to help. 👍️