Change dynamic array to sort by last 4 digits

[Newbie73]

Hello, I got the following formula:

=LET(src,Data, fsrc,FILTER(src,INDEX(src,,1)<>""), shortID,INDEX(fsrc,,1), dates,INDEX(fsrc,,12), maxdate,MAX(dates),
uidmd,SORT(UNIQUE(FILTER(shortID,dates=maxdate))), uidnmd,SORT(UNIQUE(VSTACK(shortID,uidmd),,1)),
utd,FILTER(fsrc,ISNUMBER(MATCH(shortID,uidmd,0))), nutd,FILTER(fsrc,NOT(ISNUMBER(MATCH(shortID,uidmd,0)))),
VSTACK(SORT(utd,{1,12},{1,-1}),EXPAND("",,COLUMNS(fsrc),""),VSTACK(SORT(nutd,{1,12},{1,-1}))))

Which works as intended (bonus point if you can make it even more efficient!), organising data from the Data sheet but I would like to respect all the formatting (for example, separating values that have the latest date bring and then bringing all results from that same ID (Column B) from values that don't have the latest date (that's what the blank row at row 36 is) and sorting in it but doing it with the last 4 digits of column B (at the moment is doing with all the digits, 6).

Example spreadsheet with wanted results done manually provided:

Book10.xlsx

Let me know if I didn't explained correctly or if you need any more information, thank you in advance!

 

[djclements]

Try using SORTBY instead of SORT, so you can sort by RIGHT(id, 4), then by id, then by date. For example:

=LET(
    src, Data,
    fsrc, FILTER(src, INDEX(src,, 1)<>""),
    shortId, INDEX(fsrc,, 1),
    dates, INDEX(fsrc,, 12),
    maxDate, MAX(dates),
    uIdmd, UNIQUE(SORT(FILTER(shortId, dates=maxDate))),
    incl, ISNUMBER(XMATCH(shortId, uIdmd,, 2)),
    utd, FILTER(fsrc, incl),
    nutd, FILTER(fsrc, NOT(incl)),
    VSTACK(
        LET(id, INDEX(utd,, 1), SORTBY(utd, RIGHT(id, 4), 1, id, -1, INDEX(utd,, 12), -1)),
        EXPAND("",, COLUMNS(fsrc), ""),
        LET(id, INDEX(nutd,, 1), SORTBY(nutd, RIGHT(id, 4), 1, id, -1, INDEX(nutd,, 12), -1))
    )
)

Regarding efficiency, UNIQUE(SORT(...)) is faster than SORT(UNIQUE(...)), and XMATCH with the [search_mode] set to 2 (binary search) is faster than MATCH. Also, you can add the blank row without having to split and restack the data. Simply use the EXPAND function as follows:

=LET(
    src, Data,
    fsrc, FILTER(src, INDEX(src,, 1)<>""),
    rws, ROWS(fsrc),
    dates, INDEX(fsrc,, 12),
    maxDate, MAX(dates),
    xdates, EXPAND(dates, rws+1,, maxDate),
    xsrc, EXPAND(fsrc, rws+1,, ""),
    shortId, INDEX(xsrc,, 1),
    uIdmd, UNIQUE(SORT(FILTER(shortId, xdates=maxDate))),
    isMax, ISNUMBER(XMATCH(shortId, uIdmd,, 2)),
    SORTBY(xsrc, isMax, -1, VSTACK(EXPAND(0, rws,, 0), 1), 1, RIGHT(shortId, 4), 1, shortId, -1, xdates, -1)
)

Cheers!

 

[Newbie73]

Thank you so much djclements! Working perfectly

 

[djclements]

You're welcome!