COLLECTMOD() formula

L_123

=COLLECTMOD([Return Range],"Modify Return Range",[Range 1],"Modify Range 1", "Range 1 Criteria", ...)

Leave blank if there is no modification to the range.

Paul Newcome

If I am reading this correctly, it looks like this is essentially giving us the ability to nest a COLELCT inside of a COLLECT?

L_123

Sort of.

The most basic example of:

=join(collectmod(A:A,substitute(@cell,1,2),B:B,,"X"),", ")

Would return:

2, 3, 2, 2, 3, 2

Paul Newcome

Couldn't you already do that with this?

=SUBSTITUTE(JOIN(COLLECT(A:A, B:B, "X"), ", "), "1", "2")

L_123

You're correct. I was trying to think of a way to modify the original value based of the results in the other columns.

Maybe it would be more like

COLLECTMOD([Return Range],"Modify Return Range",[Range 1], "Range 1 Criteria", "Return Range Modifier",...)