Forum Discussion

Debbie_Wood's avatar
Debbie_Wood
Copper Contributor
Oct 09, 2023

Formula to look at varying number of cells and return latest date.

I have a large spreadsheet that lists order numbers in column A and the date that each parcel within that order was dock confirmed in column B.  I need to create a formula to look at the dates in column B associated with the order number and return the latest date.  There may only be one parcel in an order or there could be hundreds.  I was wanting to create a column C that would contain the formula next to the row with the order number, see below. Can anyone help?  

 

 

excel
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Oct 10, 2023

    create temp table aa as
    select *,fillna(`Order`) ord from last_date;
    create temp table bb as
    select ord,max(`Date`) latest_date from aa group by ord;
    create temp table cc as
    select * from aa left join bb on aa.`Order`=bb.ord;
    select colExclude[\bord\b] from cc;

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Oct 10, 2023

    Debbie_Wood If you a Microsoft365 user then could try below dynamic array formula-

    =LET(
i,SCAN(A2,A2:A16,LAMBDA(a,x,IF(x="",a,x))),
MAP(A2:A16,LAMBDA(t,IF(t="","",TAKE(SORT(FILTER(B2:B16,i=t),1,-1),1)))))

    See the attached file.

     

     

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 10, 2023

    Debbie_Wood 

    =IF(A2<>"",MAX(INDEX($B$2:$B$26,ROW(B2)-1):INDEX($B$2:$B$26,IFERROR(SMALL(IF(A2:$A$26<>"",ROW(A2:$A$26)-1),2)-1,COUNT($B$2:$B$26)))),"")

     

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

Resources