Index, collect, Max

William Hall

Need help selecting the most recent two notes into a single field based on the current date.

Data: Grid 1

Desired output example: one Site with the last repair two notes in the same field.

Current notes field formula:

? Grid 2 -MAX(COLLECT AND INDEX(MATCH variations with no success. Index(collect(IF(D6 ="","",TEXT(Date,"d mmm yy")&": "&C6&" - "&

Toufong Vang

Hi, @William Hall,

Here's one approach.

If you create an auto-number column, you can use LARGE() to identify the two most recent notes. In that column, the newest notes will always have higher values. Your source sheet would look something like the one below.

Identify the Row that Has the Newest Notes

The most recent note for a site will be the one with the largest number LARGE( range, 1) . The next most recent note will have the second largest number LARGE( range, 2). The expressions below will retrieve the auto-number for those rows.

LARGE(COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row),1)

LARGE(COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row),2)

The expression to retrieve the next most recent note will fail, however, then there is only one note for the site. So we need to catch that error with IFERROR().

IFERROR(LARGE(COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row),2),0)

Retrieve the Notes

Now that you have the AutoNum, you know where the newest notes are for that site. Use that information to retrieve the notes.

COLLECT({Source Sheet Notes}, {Source Sheet AutoNum}, IFERROR(LARGE( COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row), 2), 0) 

COLLECT({Source Sheet Notes}, {Source Sheet AutoNum}, LARGE( COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row), 1) )

COLLECT() must be used inside of another function so we use JOIN(). You can use INDEX() but then you'll need to account for instances where MATCH() returns nothing.

NOTES2 = JOIN(COLLECT({Source Sheet Notes}, {Source Sheet AutoNum}, LARGE( COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row), 2) ))

NOTES1 = JOIN(COLLECT({Source Sheet Notes}, {Source Sheet AutoNum}, LARGE( COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row), 1) ))

Retrieve the Dates

DATE2 = JOIN(IFERROR(DATEONLY(COLLECT({Source Sheet Date}, {Source Sheet AutoNum}, IFERROR(LARGE(COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row),2),0))),""))

DATE1 = JOIN(IFERROR(DATEONLY(COLLECT({Source Sheet Date}, {Source Sheet AutoNum}, IFERROR(LARGE(COLLECT({Source Sheet AutoNum},{Source Sheet Index},[Index onTargetSheet]@row),1),0))),""))

Assemble Your Formula

Put the pieces together and you're done!

COMBINED_NOTES = DATE2 + " : " + NOTES2 + CHAR(10) + DATE1 + " : " + NOTES1