Previous Parent Formula

Options
Ramsay Zaki
Ramsay Zaki ✭✭✭✭✭✭

I would like to find the value of the "previous parent". The number of child rows between each parent could vary. Any thoughts how to do this?



Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Ramsay Zaki

    There may be a more succinct way to do this, but what I would do is add three helper columns in your sheet:

    • An Auto-Number column that will product a row ID as you create new rows
    • A Parent column that will identify if the current row is a Parent or Child row
    • A Rank column which will use the Parent column and Row ID column to assign a Number to each Parent row, in order.


    Parent Column Formula:

    =IF(COUNT(CHILDREN([Primary Column]@row)) > 0, "Parent", "Child")

    Rank Formula:

    =IFERROR(RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], Parent:Parent, "Parent"), 1), "")


    Once you have these columns set up, you can use an INDEX(COLLECT to find the previous Parent row by subtracting 1 off of the current Rank in this Parent row:

    INDEX(COLLECT(Value:Value, Rank:Rank, Rank@row - 1), 1)


    In my sheet, I've put this in an IF statement, so it will only do this if the current row is not the very first Parent, and if the current row is a Parent:

    =IF(AND(Rank@row <> 1, Parent@row = "Parent"), INDEX(COLLECT(Value:Value, Rank:Rank, Rank@row - 1), 1))

    Then you can set this to be a Column Formula.

    Cheers,

    Genevieve