Find multiple matches in a dataset, and return all matches in a single row

1
votes

What array formula would work for this?

Test Sheet: Open

Current Data Structure

  • Contains a running list of names and when they started, ended training.
|   A   |     B      |     C    |
| John  | StartDate1 | EndDate1 |
| Adam  | StartDate3 | EndDate3 |
| John  | StartDate2 | EndDate2 |
| Ted   | StartDate5 | EndDate5 |
| Adam  | StartDate4 | EndDate4 |

Expected Results

  • Unique column of names in column E =UNIQUE(A2:A)
  • Next to the unique name, display every StartDate & EndDate that matches the unique name.
|   E   |     F      |     G    |     H      |     I    |
| John  | StartDate1 | EndDate1 | StartDate2 | EndDate2 |
| Adam  | StartDate3 | EndDate3 | StartDate4 | EndDate4 |
| Ted   | StartDate4 | EndDate4 |            |          |

What I have tried

=FILTER(B2:C,A2:A = E2)

  • Does not return on a single row. ❌
  • Does not work with ARRAYFORMULA. ❌

=TRANSPOSE(FILTER(B2:C,A2:A = E2:E))

  • Returns all StartDates on a single row, and all End Dates on the next row. ❌
    • It should return on a single row (StartDate,EndDate,StartDate,EndDate, etc)
  • Does not work with ARRAYFORMULA. ❌

=ARRAYFORMULA(VLOOKUP(E2:E,A2:C,{2,3}))

  • Returns the first match only ❌
  • Works with array formula. ✔️

What am I doing wrong? Is there a better arrayformula that can display every start and end date that matches a unique name in a row?

Thanks for your help!

1
google-sheetsgoogle-sheets-formulaarray-formulas

1 Answers

1
votes

use:

=INDEX(SPLIT(FLATTEN(QUERY(QUERY(IF(A3:A="",,{A3:A, "×"&B3:B&"×"&C3:C}), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9)), "×"))

enter image description here