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 

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
    ToTable

Resources