Column Formula to populate a Helper Column

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)), ""))

Find more posts tagged with

Formulas

columns

Accepted answers

All comments

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.

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.
Create a report based on this sheet and group it by OPP #. Use the report to compare to the OPP #s with known issues.
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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.