Alternating contacts

kioshi43

Hi,

I'm trying to see if I can assigned alternating contacts for rows for some children rows. What I would love to do is find a way to reference the last children row contact from one location and then start off with the other but I'm having a bit of a struggle.

Initially my plan was to create some auto generated system numbers and then use a match formula to identify what row number that system generated number would be at. When I tried doing an iseven formula and tried alternating the contacts that way, the numbers were lopsided since not every location has the same amount of children floors.

I then tried to see if I could change the match column formula to reference the parent of the auto generated numbers but that did not pan out either.

Anyone have any thoughts on how tackle this? Any help would be appreciated. Thanks!

Genevieve P.

Hi @kioshi43

It sounds like you just need to add another helper column in, to identify if the current row is a Child row or a Parent row!

Try adding in a column with this formula:

=PARENT([Primary Column]@row)

This will return a blank cell if that row is a Parent, or the Parent Name if the row is a Child. Then you can reference this in a COLLECT Function so you can filter out all of the Parent Rows and only look at the auto-number if the row is a Child:

RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent Name]:[Parent Name], <>""), 1)

Then you can embed this into an IF statement to say that IF the Child Rank based on the Row ID (the auto-number) is Even, return the text "Even", otherwise return "Odd".

Full formula:

=IFERROR(IF(ISEVEN(RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent Name]:[Parent Name], <>""), 1)), "Even", "Odd"), "")

Then you can use if the cell says "Even" or "Odd" to auto-assign someone in your Assigned To column. You could also change out the IF statement output to be an email address or someone's name, if you prefer.

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Genevieve P.

Hi @kioshi43

It sounds like you just need to add another helper column in, to identify if the current row is a Child row or a Parent row!

Try adding in a column with this formula:

=PARENT([Primary Column]@row)

This will return a blank cell if that row is a Parent, or the Parent Name if the row is a Child. Then you can reference this in a COLLECT Function so you can filter out all of the Parent Rows and only look at the auto-number if the row is a Child:

RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent Name]:[Parent Name], <>""), 1)

Then you can embed this into an IF statement to say that IF the Child Rank based on the Row ID (the auto-number) is Even, return the text "Even", otherwise return "Odd".

Full formula:

=IFERROR(IF(ISEVEN(RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent Name]:[Parent Name], <>""), 1)), "Even", "Odd"), "")

Then you can use if the cell says "Even" or "Odd" to auto-assign someone in your Assigned To column. You could also change out the IF statement output to be an email address or someone's name, if you prefer.

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

kioshi43

That works out, thank you! SOrry, I thought I had replied to this and only now realized I didn't. Thank again!