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 I am facing. Thanks.
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.
9 Replies
One more variant is with Power Query
Script is
let Source = Excel.CurrentWorkbook(){[Name="SampleData"]}[Content], #"Remove Unused" = Table.SelectColumns(Source,{"Grp", "Task ID"}), #"Added Index" = Table.AddIndexColumn(#"Remove Unused", "Index", 0, 1, Int64.Type), #"Removed Duplicates" = Table.Distinct(#"Added Index", {"Grp", "Task ID"}), #"Remove Index" = Table.RemoveColumns(#"Removed Duplicates",{"Index"}), #"Grouped Rows" = Table.Group( #"Remove Index", {"Grp"}, {{"Tasks", each List.Sort(_[Task ID] ) }} ), ToTable = Table.FromColumns(#"Grouped Rows"[Tasks], #"Grouped Rows"[Grp]) in ToTableAs 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
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.
