Forum Discussion
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...
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.
I'd create a pivot table - see the attached version.
- Tks for the reply. Is there anyway to use formula for Excel 2016? I tried not to use pivot table if possible.
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.
- Tks for the solutions. Appreciate it. Solved.
Advanced Filter would be another solution, but here is one using formulas:
- Thanks Hans, Appreciate it. It works too...
