fixing this nested IF , INDEX/MATCH formula

MarkCep

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.

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.