List All Matched Cells that contain a given value

[Alecs]

Hello guys,

I'm looking for a formula that can match all values from a range and put the result in the correspondent column. This is an example. I've tried to make it as easy as possible.

Here is how this works:

In Table 1: I will insert the values and assign each task to a ROBOT. Each task has a status. 

If the status is solved it disappears. If not solved, then all the values are concatenated in column G.

In Table 2: the concatenated text is displayed in different stages (on the rows - it is not possible to display it in columns). Rows and columns can have blank cells between them.

Table 3 needs to have the magic formula that I need (in J13 cell).

This formula needs to filter and display all rows and columns from Table 2 that contain the "robot A" word and put it in the robot A column. Same for robot B column and robot C column.

So in the end, robot A column (from J13 to J22) needs to display 5 entries. 

robot B column needs to display 6 entries, and so on. 

If one entry is deleted or changed to "Solved" in Table 1 it must not appear in table 3 anymore.

question: Is it possible to display all the entries in table 3 columns by DATE? or by STAGE and TASK numbers? That would be amazing. I've attached the excel file below.

Many thanks!

Alex

[#2 · Accepted solution]

@Alecs This would be even better with the new text functions but here is a version using current Excel 365 functionality:

=LET(in,J3:R9,rs,ROWS(in),cs,COLUMNS(in),s,SEQUENCE(rs*cs,1,0),col,INDEX(in,QUOTIENT(s,cs)+1,MOD(s,cs)+1),FILTER(col,ISNUMBER(SEARCH(P$12,col)),""))

[#3]

That's perfect! thanks a lot!