Column Formula to populate a Helper Column

Annaleah Morrow

EDIT: I just realized this is listed in the Add Ons and Integrations section rather than elsewhere, but I cannot move it.

I have created separate helper columns to return the value in [Plan Start Helper] for any of the matching [Opp #] where [Plan] contains the word "Plan" (I have other helper columns that need to search for Test, Architect, etc. The [Plan] column has spaces and may have more than one word.

Right now I am getting the expected 01/06/2025 for rows 1-8 (same Opp group), but when I go to rows 9-17, I expect to get a return of 07/15/24 for all rows, but I'm only getting that return on row 13 (which has "plan" in the [Phase]column @row), and returning 02/24/2025 for all other cells in that column for that group Opp # even though that is not a date associated with rows 9-17 at all. 2/24/25 continues into the next group as well. I have included a sample of the first three groups, based on OPP #.

Any ideas would be welcome!

Column Formula:

=IF(CONTAINS("Plan", Phase@row), [Start Date]@row, IF(COUNTIF([OPP #]:[OPP #], [OPP #]@row) > 0, INDEX([Start Date]:[Start Date], MATCH("Plan", COLLECT(Phase:Phase, [OPP #]:[OPP #], [OPP #]@row), 0)), ""))

Carson Penticuff

I am a little confused by your request/formula. First off, this is what it looks like you are trying to accomplish, see if it works for you. If it does, I guess you can ignore my questions below.

=IF(CONTAINS("Plan", Phase@row), [Start Date]@row, INDEX(COLLECT([Start Date]:[Start Date], [OPP #]:[OPP #], [OPP #]@row, Phase:Phase, CONTAINS("Plan", @cell)), 1))

If this isn't what you are looking for, here are the things I am a little confused about:

"where [Plan] contains the word "Plan""

Did you mean [Phase] instead of [Plan]? There is no [Phase] column in your screenshot, and the formulas you listed does not reference it either.

IF
  (
    COUNTIF([OPP #]:[OPP #], [OPP #]@row) > 0,
……

This condition will always return true, as it is impossible to return 0, so I'm not sure of the function of this IF statement.

Hopefully this helps.

Annaleah Morrow

Hi, @Carson Penticuff . Thank you for your analysis!

You are spot on. Sorry, in my screenshot it shows [Phase] is the column header, and "Plan" is the word I'm looking for. Interestingly, your formula worked like a charm for most of the groups of matching [Opp #], but there are some when I spot checked through the first 300 or so lines that are still wrong. Any idea what would be pushing these off? I would expect that all of the [Plan Start Helper] in these matching [Opp #] groups have the highlighted [Start Date] listed in the [Phase] column containing "Plan" at that row.

Carson Penticuff

For the previous formula I posted, I was kind of going off of the initial formula you were using.

Any row that contains "Plan" in the [Phase] column will have the [Start Date] copied over to the [Plan Start Helper] column.

Any row that does NOT contain "Plan" in the [Phase] column will have the [Start Date] of the row that DOES contain "Plan" in the [Phase] row that ALSO matches the [OPP #] column.

The only way I would expect to see the results you are getting is if there are multiple rows with the same [OPP #] that contain "Plan" in the [Phase] column. I made the assumption that only one row for each [OPP #] would contain "Plan". Is it possible there could be multiple?

Annaleah Morrow

@Carson Penticuff there are multiple rows in [Phase] that have "Plan" for the same [Opp #], but not for the ones I highlighted. On the ones where it found multiple Plans for the same Opp it returned the first instance which is totally fine. I can't find anything different about the highlighted ones that makes sense on why these in particular are working differently. I also ran the issue through ChatGPT and it didn't provide a solution that fixed these highlighted items either.

Carson Penticuff

@Annaleah Morrow You have me stumped on this one. Are you sure there aren't any other rows somewhere else on the sheet that match OPP # for the problem sections? Maybe a different OPP # was mistyped somewhere else?

Annaleah Morrow

The OPP# is auto-generated and can't be changed. I'm stumped too! I wonder if there is a way to identify these with another row to see where there is an issue…ideas there other than manual review? There are about 15000 rows, so I can't review manually.

Carson Penticuff

I'm still not certain as to a solution, but I can think of a few troubleshooting ideas. I'm still working on the premise that there must be duplicate OPP # rows elsewhere in the sheet.

1. Do a CTRL+F search of one of the OPP # you listed above that is an issue and see if anything pops up outside of the expected range.
2. Create a report based on this sheet and group it by OPP #. Use the report to compare to the OPP #s with known issues.
3. Add a column to your sheet to count how many rows match the OPP # for each row and compare that to trouble OPP #s.
   1. =COUNTIFS([OPP #]:[OPP #], [OPP #]@row)

Annaleah Morrow

@Carson Penticuff you were right. There were additional OPP#s. The others are now fixed and I'm trying to figure out the best way to clear out the others. I wish automation would clear full rows and not just cells. Thank you for your help!