Dynamic list of row / columns based on table

[ronbal]

Hi everyone, I am trying to generate lists based on two criteria (date and initial) value on the table. Desired result will be color formatted as well based upon the criteria (processing, verifying and reviewing). Any help will be greatly appreciated.

 

[Peter Bartholomew]

That was truly a nightmare! I have pretty much got there using 365 in a long-winded, somewhat pedantic manner but I certainly won't be converting the formula to legacy Excel! I started by using

Normaliseλ
= LAMBDA(fruits, data,
    LET(
        criteria,     {"Processing", "Verifying", "Reviewing"},
        date,         CHOOSECOLS(data, 2, 4, 6),
        initials,     CHOOSECOLS(data, 1, 3, 5),
        dateList,     TOCOL(date),
        initList,     TOCOL(initials),
        fruitList,    TOCOL(CHOOSECOLS(fruits, 1, 1, 1)),
        activityList, TOCOL(IF(ROW(fruits), criteria)),
        table,        HSTACK(dateList, initList, fruitList, activityList),
        table
    )
);

to convert the table to a normalised form

Then I ploughed on with a worksheet formula from Hell using two REDUCE/STACK combinations

= LET(
      table,     Normaliseλ(fruits, data),
      dateList,  CHOOSECOLS(table,1),
      initList,  CHOOSECOLS(table,2),
      fruitList, CHOOSECOLS(table,3),
      result,    REDUCE(HSTACK("Date", initialsHdr), UNIQUE(dateList),
         LAMBDA(return,d,
            VSTACK(
               return,
               REDUCE(d, initialsHdr, LAMBDA(acc,init,
                  HSTACK(acc, FILTER(fruitList, (dateList=d)*(initList=init),"")))
               )
            )
         )
      ),
   IFERROR(result,"")
 )

The conditional formatting was achieved by writing a similar table showing the criteria rather than the fruits and using a formula to determine the CFs.