fixing this nested IF , INDEX/MATCH formula

I am having issues with getting this formula to work:

=IF([Latest comment]:[Latest comment] <> "", INDEX([Latest comment]:[Latest comment], MATCH(MAX([Notes Last Modified Date - Helper]:[Notes Last Modified Date - Helper]), [Notes Last Modified Date - Helper]:[Notes Last Modified Date - Helper]), 1), 0)

In the Notes last Modified column, there are several changes that happen to change the modified date, and not all of the rows are a result of a comment being made. I am looking to only pick up the rows that have a comment in them and get the most recent comment.

Help, my goal is to capture the latest comment to then use in a roll up report.

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for context?

MarkCep

Here are some screen shots

Paul Newcome

You will need a helper column that flags which row to pull as there is now way to do those particular comparisons in a single formula. You would then use a basic INDEX/MATCH to pull the comment based on the helper column being flagged.

=IF(AND([Latest Comment]@row <> "", [Notes Last Modified Date - Helper]@row = MAX(COLLECT([Notes Last Modified Date - Helper]:[Notes Last Modified Date - Helper], [Latest Comment]:[Latest Comment], @cell <> ""))), 1)

=INDEX([Latest Comment]:[Latest Comment], MATCH(1, [Helper Column]:[Helper Column], 0))

MarkCep

Paul, I take it the if statement is to be put in the "Helper Column". which is a column type/checkbox

When I do I get an error message #INVALID DATA TYPE

Was I in the right direction of your advice?

Paul Newcome

That is correct. Is the [Notes Last Modified Date - Helper] column set as a date type column?

MarkCep

The [Notes Last Modified Date - Helper] is the system generated modified column.

Thanks

Mark

Paul Newcome

Lets try this then:

=IF(AND([Latest Comment]@row <> "", DATEONLY([Notes Last Modified Date - Helper]@row) = DATEONLY(MAX(COLLECT([Notes Last Modified Date - Helper]:[Notes Last Modified Date - Helper], [Latest Comment]:[Latest Comment], @cell <> "")))), 1)

MarkCep

Huge thanks for giving this a shot, but it gives me #invalid data type

Paul Newcome

Ok. Lets try some temporary changes for troubleshooting. Change this helper column into a date type column and enter

=DATEONLY(MAX(COLLECT([Notes Last Modified Date - Helper]:[Notes Last Modified Date - Helper], [Latest Comment]:[Latest Comment], @cell <> "")))

MarkCep

If I do that, would I lose the time aspect, which is important to capturing the most recent row that has had a comment added. There may be several rows in a day that get comments made.

Paul Newcome

Yes. But as I said, this is only temporary for some troubleshooting to try to figure out where exactly the error is coming from, fix that, then go back to trying the overall solution again.

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.