Search code examples
google-sheetsgoogle-sheets-formula

Data recovery based on other data

In my file, I list the championship days by group: https://docs.google.com/spreadsheets/d/1zYplMk2HOs-AC84sgRH1hDMH5-Yq2DuZ-XzXW3R0kBI/edit?usp=sharing

As the days do not correspond to the same dates for the different hens, I would like to be able to retrieve the hens by date (I manually highlighted the expected result in yellow).

And to go further, also add in the cell following the hen the number of the corresponding day (see the cells in orange for the expected result)

I tried with FIND functions but this is not suitable on the one hand because it means doing a large number of IF tests and on the other hand, when the date is not found, it returns an error.

How could I do it (if possible by formula but otherwise by script)?

THANKS

Solution

  • Here's one approach you may test out:

    =let(Σ;sort(unique(tocol(B2:M8;1)));
     Λ;reduce(;B2:M8;lambda(a;c;vstack(if(iserror(a&"");;a);if(len(c);hstack(c;index(A:A;row(c));index(1:1;column(c)));))));
     map(Σ;lambda(x;hstack(x;torow(filter(choosecols(Λ;2;3);choosecols(Λ;1)=x))))))

    enter image description here

    • formula output mismatching with your expected output table for quite a few instances which you may need to check upon...