Forum Discussion

choongko's avatar
choongko
Brass Contributor
Oct 02, 2021
Solved

Extract list with only allowing duplicates if item is shared

Hope someone can help me with a solution to extract the list (TaskID) assigned to the groups.  The TaskID should only duplicate if they are assigned to more than one group.    This part was the issue...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 02, 2021

    choongko 

    That could be

    J59:

    =IFERROR( INDEX(
  SampleData[[Grp]:[Grp]],
 AGGREGATE(15, 6,
      1/(COUNTIF( $I$59:I59,  SampleData[[Grp]:[Grp]] )=0)*
     ( ROW( SampleData[[Grp]:[Grp]] ) -
       ROW(SampleData[[#Headers],[Grp]] ) ),
  1)
 ), "")

    drag to the right till empty cells appear

    J60:

    =IFERROR( INDEX( SampleData[[Task ID]:[Task ID]],
     AGGREGATE(15, 6,
       1/
         ( SampleData[[Grp]:[Grp]] = J$59 ) /
         ( COUNTIF(J$59:J59, SampleData[[Task ID]:[Task ID]] ) = 0)*
     ( ROW(SampleData[[Grp]:[Grp]] ) -ROW(SampleData[[#Headers],[Grp]]) ),
      1 ) ),
 "")

    drag to the right and entire line down.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 02, 2021

choongko 

As variant with formulae

J13:

=TRANSPOSE( UNIQUE( SampleData[Grp] ) )

J14:

=SORT( UNIQUE( FILTER( SampleData[[Task ID]:[Task ID]], SampleData[[Grp]:[Grp]]=J13 ) ))

and drag to the right

Resources