Removing Complex Duplicates

[IML_56]

Hello all im not sure if this can be done or not, but i have some data with a lot of rows some times there are duplicates. I want to delete a specific value in that duplicate column while retaining the rest of the information. Can this be done in power query?



My example: I want the duplicate row to have the item in boxes removed but keep everything.

Project	Funding	Boxes
AB	$10	10
AB	$100	10
CD	$20	5
EF	$15	20

Result:

Project	Funding	Boxes
AB	$10	10
AB	$100
CD	$20	5
DE	$15	20

Thanks,
Ian

 

[sandy666]

with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Index2 = Table.AddIndexColumn(Source, "Index", 1, 1),
    SortD = Table.Sort(Index2,{{"Boxes", Order.Descending}}),
    RD = Table.Distinct(SortD, {"Boxes"}),
    SortA = Table.Sort(RD,{{"Index", Order.Ascending}}),
    TSC = Table.SelectColumns(SortA,{"Boxes", "Index"}),
    Mrg = Table.NestedJoin(Index,{"Index"},TSC,{"Index"},"Exp",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Mrg, "Exp", {"Boxes"}, {"Boxes.1"}),
    TSC2 = Table.SelectColumns(Expand,{"Project", "Funding", "Boxes.1"})
in
    TSC2

Project	Funding	Boxes		Project	Funding	Boxes.1
AB	10	10		AB	10	10
AB	100	10		AB	100
CD	20	5		CD	20	5
EF	15	20		EF	15	20
xx	45	30		xx	45	30
xx	89	30		xx	89
xx	345	30		xx	345
dd	543	125		dd	543	125
www	12	909		www	12	909

if you need you can change Boxes.1 to Boxes in last step

 

[sandy666]

a bit shorter

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    SortD = Table.Sort(Index,{{"Boxes", Order.Descending}}),
    RD = Table.Distinct(SortD, {"Boxes"}),
    TSC = Table.SelectColumns(RD,{"Boxes", "Index"}),
    Mrg = Table.NestedJoin(Index,{"Index"},TSC,{"Index"},"Exp",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Mrg, "Exp", {"Boxes"}, {"Boxes.1"}),
    TSC2 = Table.SelectColumns(Expand,{"Project", "Funding", "Boxes.1"})
in
    TSC2

 

[IML_56]

Try this macro assuming your data is in columns A, B and C:

Sub RemoveDups()
    Application.ScreenUpdating = False
    Dim LastRow As Long, RngList As Object, rng As Range, key As Variant, fVisRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each rng In Range("A2:A" & LastRow)
        If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, Nothing
        End If
    Next
    For Each key In RngList
        If WorksheetFunction.CountIf(Range("A:A"), key) > 1 Then
            With ActiveSheet
                .Cells(1, 1).CurrentRegion.AutoFilter 1, key
                fVisRow = .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
                .Range("C" & fVisRow + 1 & ":C" & LastRow).SpecialCells(xlCellTypeVisible).ClearContents
            End With
        End If
    Next key
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

This worked great. Thank you so much.

 

[IML_56]

a bit shorter

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    SortD = Table.Sort(Index,{{"Boxes", Order.Descending}}),
    RD = Table.Distinct(SortD, {"Boxes"}),
    TSC = Table.SelectColumns(RD,{"Boxes", "Index"}),
    Mrg = Table.NestedJoin(Index,{"Index"},TSC,{"Index"},"Exp",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Mrg, "Exp", {"Boxes"}, {"Boxes.1"}),
    TSC2 = Table.SelectColumns(Expand,{"Project", "Funding", "Boxes.1"})
in
    TSC2

Thank you!

 

[sandy666]

You are welcome

here is more flexible

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    SortD = Table.Sort(Index,{{"Project", Order.Ascending}}),
    RD = Table.Distinct(SortD, {"Project"}),
    TSC = Table.SelectColumns(RD,{"Project", "Index"}),
    Mrg = Table.NestedJoin(Index,{"Index"},TSC,{"Index"},"Exp",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Mrg, "Exp", {"Index"}, {"Index.1"}),
    IF = Table.AddColumn(Expand, "Custom", each if [Index.1] = null then null else [Boxes]),
    TSC2 = Table.SelectColumns(IF,{"Project", "Funding", "Custom"})
in
    TSC2

 

[mumps]

Glad to help.