excelexcel-formula

Combine most recent 5 records into one cell in Excel

I've got a table of distinct people going down in Sheet1 like this:

enter image description here

I've got another table in Sheet2 where Date goes down but the distinct people goes across, like this:

enter image description here

How do I create the Form column in Sheet1 which is based on the 5 latest entries? I'm happy to manually edit the formula (from B:C to B:?) when more people get added to Sheet1 and Sheet2

Solution

  • Here is one way you could try:

    enter image description here

    =MAP(A2:A3, LAMBDA(x,
 LET(
     a, D1:F8,
     b, FILTER(DROP(a,1,1),DROP(TAKE(a,1),,1)=x),
     c, TAKE(SORT(FILTER(HSTACK(DROP(TAKE(a,,1),1),b),b>0),,-1),5,-1),
     CONCAT(SORTBY(c,SEQUENCE(ROWS(c)),-1)))))

    Update alternative, can use the following to copy down as far one needs:

    =LET(
     a, FILTER(E$2:F$8,E$1:F$1=A2),
     CONCAT(TAKE(FILTER(HSTACK(D$2:D$8,a),a<>""),-5,-1)))

    Using the MAP() to spill:

    =MAP(A2:A144,LAMBDA(x,
 LET(
     a, FILTER(E2:EQ178,E1:EQ1=x),
     CONCAT(TAKE(FILTER(HSTACK(D2:D178,a),a<>""),-5,-1)))))