This proposal employs three helper columns, which may be moved and/or hidden for aesthetic purposes.
On the Data Set sheet column H, Group members, is populated using: =B2&C2&D2&E2&F2&G2
Column I, Group #, is populated using: =MATCH(H2,H$2:H2,0)
On the Output sheet column A is populated using: =IFERROR(AGGREGATE(15,6,'Data Set'!I$2:I$25,ROWS(A$1:A1)),"")
Column B is populated using: =IF(A5="","",INDEX('Data Set'!A$2:A$25,AGGREGATE(15,6,(ROW(A$2:A$25)-ROW(A$1))/('Data Set'!I$2:I$25=A5),COUNTIFS(A$5:A5,A5))))
Columns C:AB are populated using: =IF(A5="","",INDEX('Data Set'!J$2:J$25,MATCH($B5,'Data Set'!$A$2:$A$25,0)))
Let us know if you have any questions.
Bookmarks