List All Matched Cells that contain a given value


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!


2 Replies
best response confirmed by Alecs (Contributor)

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

That's perfect! thanks a lot!