Is it possible to evaluate multiple predecessors and match to a cell value in another column?

TomG · 09/27/22

I need to correlate a predecessor row to a work order number in a different column within the same sheet. There can be multiple predecessors. Is there a way to have a formula evaluate multiple predecessors and show a corresponding work order number?

Here is a screenshot, I'm trying to figure out if a formula can be used to populate the work order numbers (cell in yellow), based on the values in the predecessor column when there are multiple predecessors.

For each predecessor value, want to look up the work order number on the corresponding row.

Jason Tarpinian · 10/04/22

I've done something similar, and depending on how simple your predecessors are, the solution could be simple, or make your head spin. For the super simple version (2 predecessors MAX) try this:

Add "Row #" column to make sure your lookups match your Predecessor numbers. This formula will match if you shift rows around and such:

=MATCH([Work Order]@row, [Work Order]:[Work Order], 0)

You must break out the Predecessors into individual columns. I can't seem to change them to values referencing the Predecessors column directly:

Pred#1: =IFERROR(LEFT(Predecessors@row, FIND(",", Predecessors@row) - 1), Predecessors@row)

Pred#2: =IF(FIND(",", Predecessors@row) > 0, RIGHT(Predecessors@row, LEN(Predecessors@row) - FIND(",", Predecessors@row)), "")

Then you can use those values to bring in the corresponding work orders with an INDEX(MATCH()) combination.

=IFERROR(INDEX([Work Order]:[Work Order], MATCH(VALUE([Pred#1]@row), [Row #]:[Row #], 0)), "") + IF(ISBLANK([Pred#2]@row), "", ", ") + IFERROR(INDEX([Work Order]:[Work Order], MATCH(VALUE([Pred#2]@row), [Row #]:[Row #], 0)), "")

As you can see in my sample above however, when you get 3+ predecessors this solution doesn't work. It is possible, but the formulas will start getting much more complicated involving the MID() function and lots of FIND() commas. It would get even trickier still if you have any lag durations in your predecessors (such as 2FS-2d... etc). So if you can keep it simple, the above solution should work for you, above and beyond that is going to take a lot more engineering time to work out well.

I hope this at least gets you in the right direction!

Jason Tarpinian · 10/04/22

I've done something similar, and depending on how simple your predecessors are, the solution could be simple, or make your head spin. For the super simple version (2 predecessors MAX) try this:

Add "Row #" column to make sure your lookups match your Predecessor numbers. This formula will match if you shift rows around and such:

=MATCH([Work Order]@row, [Work Order]:[Work Order], 0)

You must break out the Predecessors into individual columns. I can't seem to change them to values referencing the Predecessors column directly:

Pred#1: =IFERROR(LEFT(Predecessors@row, FIND(",", Predecessors@row) - 1), Predecessors@row)

Pred#2: =IF(FIND(",", Predecessors@row) > 0, RIGHT(Predecessors@row, LEN(Predecessors@row) - FIND(",", Predecessors@row)), "")

Then you can use those values to bring in the corresponding work orders with an INDEX(MATCH()) combination.

=IFERROR(INDEX([Work Order]:[Work Order], MATCH(VALUE([Pred#1]@row), [Row #]:[Row #], 0)), "") + IF(ISBLANK([Pred#2]@row), "", ", ") + IFERROR(INDEX([Work Order]:[Work Order], MATCH(VALUE([Pred#2]@row), [Row #]:[Row #], 0)), "")

As you can see in my sample above however, when you get 3+ predecessors this solution doesn't work. It is possible, but the formulas will start getting much more complicated involving the MID() function and lots of FIND() commas. It would get even trickier still if you have any lag durations in your predecessors (such as 2FS-2d... etc). So if you can keep it simple, the above solution should work for you, above and beyond that is going to take a lot more engineering time to work out well.

I hope this at least gets you in the right direction!

TomG · 10/04/22

Thanks Jason!

This is very helpful and is a significant step in the right direction. I'm not sure we can keep it to only 2 predecessors, so it looks like I have some formula fun ahead of me. Of course the current requirement is to allow for lag values as well but that may be a nice to have instead of a must have.

I really appreciate the help and hopefully this will help others as well, I've seen other posts expressing similar challenges.

Have a great day!

TomG

TomG · 02/24/23

Just an update on this, @Jason Tarpinian solution works great. We had a need to expand that to up to 10 predecessors. In order to do that, I had to add a comma as a delimiter between values and at the end of the final value. Then used the following formulas to extract the individual values (Used a column called Constraints for the entry of the multiple predecessor values):

Contraint 1: =IFERROR(LEFT(Constraints@row, FIND(",", Constraints@row) - 1), Constraints@row)

Constraint 2: =IFERROR(MID(Constraints@row, (FIND([Constraint1]@row, Constraints@row) + LEN([Constraint1]@row) + 1), (FIND(",", Constraints@row, (FIND(", ", Constraints@row, (FIND([Constraint1]@row, Constraints@row))) + 1))) - FIND(", ", Constraints@row, (FIND([Constraint1]@row, Constraints@row))) - 1), "")

Constraint 3: =IFERROR(MID(Constraints@row, IF(LEN([Constraint2]@row) <> 0, IF(LEN([Constraint2]@row) <> 0, FIND([Constraint2]@row, Constraints@row) + LEN([Constraint2]@row) + 1)), (FIND(",", Constraints@row, IF(LEN([Constraint2]@row) <> 0, (FIND([Constraint2]@row, Constraints@row) + LEN([Constraint2]@row) + 1))) - (IF(LEN([Constraint2]@row) <> 0, FIND([Constraint2]@row, Constraints@row) + LEN([Constraint2]@row) + 1)))), "")

Constraint 4: =IFERROR(MID(Constraints@row, IF(LEN([Constraint3]@row) <> 0, IF(LEN([Constraint3]@row) <> 0, FIND([Constraint3]@row, Constraints@row) + LEN([Constraint3]@row) + 1)), (FIND(",", Constraints@row, IF(LEN([Constraint3]@row) <> 0, (FIND([Constraint3]@row, Constraints@row) + LEN([Constraint3]@row) + 1))) - (IF(LEN([Constraint3]@row) <> 0, FIND([Constraint3]@row, Constraints@row) + LEN([Constraint3]@row) + 1)))), "")

For additional constraints, the formula for constraints 3 & 4 can be copied and modified. Definitely not a pretty solution, but it is working very effectively.

Hope this is helpful to others, have a great day!

TomG

Is it possible to evaluate multiple predecessors and match to a cell value in another column?

Best Answer

Answers

Help Article Resources

Categories