Forum Discussion

fmdon's avatar
fmdon
Copper Contributor
May 19, 2024
Solved

EXCEL QUESTION

I have a spreadsheet with 3 tabs, in each tab I have numbered boxes. They are now separated per tabs because they are in different stages/status of a specific process, for example most recently date...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 28, 2024

fmdon 

This is a 365 approach that uses a single formula (in this case in cell Q2)

The formula is

= LET(
      combined, VSTACK(
          EXPAND(dateExp, , 4,"Exp"),
          EXPAND(dateImp, , 4,"Imp"),
          EXPAND(dateEmpty,,4,"Empty")
      ),
      sorted, SORT(combined, 2,-1),
      REDUCE({"Box","Date","Location","Status"}, box,
          LAMBDA(a,b,
              VSTACK(a, XLOOKUP(b, TAKE(sorted,,1), sorted))
          )
      )
  )

Before stacking the tables an addition field is introduced to record the status of each record.  The combined array is sorted to bring the most recent events to the top for XLOOKUP.  REDUCE is needed because the result to return is an array of arrays (pretty much like every other calculation you will ever encounter!)

Resources