Combining two columns into one ordered list

Question

Hi allProblem trying to solve.I have a table of data like as follows:I know how to VSTACK, but how would I order by project and only repeat the project once with any matching rows in column B underneath as shown?Thank you!

oliverscheurich · Answer

=DROP(REDUCE("",UNIQUE(A1:A15),LAMBDA(u,v,VSTACK(u,v,FILTER(B1:B15,A1:A15=v)))),1)With the latest version of Excel you can use this formula.

peterbartholomew1 · Answer

A couple of more exotic solutionsThe first uses thunks to group the data by project before stacking and expanding the data.= LET(
    grouped,         GROUPBY(task, responsibility, THUNK,,0),
    responsibilityϑ, TAKE(grouped,,-1),
    taskϑ,           MAP(TAKE(grouped,,1), THUNK),
    combinedϑ,       TOCOL(HSTACK(taskϑ, responsibilityϑ)),
    EVALTHUNKARRλ(combinedϑ)
  )The second has less in the way of overkill= LET(
    project, MAP(task, LAMBDA(t, (t XLOOKUP(t, task, task)))),
    TOCOL(HSTACK(project, responsibility),3)
  )&nbsp;

Forum Discussion

Combining two columns into one ordered list

2 Replies

Resources