Previous Parent Formula

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?

Genevieve P.

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