google-sheetsspreadsheet

list of dates associated with name


What would be a good approach to report of all the dates a name occurs in a list? Can this be done with a single array formula?

Example (column A and B are input, columns C through G are to be auto-generated):

|   A     |   B    |   C    |   D     |    E    |    F    |    G    |
+---------+--------+--------+---------+---------+---------+---------+
| Episode | Stars  | Name   | Date    | Date    | Date    | Date    |
+---------+--------+--------+---------+---------+---------+---------+
| 7/24/15 | Bart   | Bart   | 7/24/15 | 7/18/15 | 8/15/15 | 3/29/15 |
| 8/09/15 | Maggie | Homer  | 1/10/15 |         |         |         |
| 7/24/15 | Marge  | Lisa   | 7/20/15 | 6/04/15 |         |         |
| 7/18/15 | Bart   | Maggie | 8/09/15 |         |         |         | 
| 1/10/15 | Homer  | Marge  | 7/24/15 |         |         |         |
| 8/15/15 | Bart   |        |         |         |         |         |
| 7/20/15 | Lisa   |        |         |         |         |         | 
| 6/04/15 | Lisa   |        |         |         |         |         |
| 3/29/15 | Bart   |        |         |         |         |         |  


   |^^^^^^|
   |      |
   |      |
   | (o)(o)
   @      _)  
    | ,___|  - Thanks Dude!
    |   /
    /___\
   /     \

Solution

  • I don't think this is easily possible in a single arrayformula. However, as an alternative you could try this formula in cell C2:

    =SORT(UNIQUE(QUERY(FILTER(B$2:B,LEN(B$2:B)))),1,1)

    Then try this formula in cell D2 and drag down:

    =TRANSPOSE(QUERY(A$2:B,"select A where B='"&C2&"'"))

    See this example sheet to see it working: https://docs.google.com/spreadsheets/d/1LRfZyxTd65gAcK92rr0JYrZLC2DG7W-75hwAsoYp-kg/edit?usp=sharing