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

 

[mumps]

I'm not familiar with Power Query but a macro can do what you want. Can there be more than 2 duplicates of any project?

 

[sandy666]

Right click on this field, Replace Values, in Replace With type null then ok but it will replace ALL duplicated values
post more representative example

 

[IML_56]

I'm not familiar with Power Query but a macro can do what you want. Can there be more than 2 duplicates of any project?

There can be more than one duplicate of the same project, and there is multiple projects that have duplicates.

 

[sandy666]

something like this?

Project	Funding	Boxes		Project	Funding	Custom
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		zz	89
xx	345	30		vv	345
dd	543	125		dd	543	125

 

[IML_56]

Right click on this field, Replace Values, in Replace With type null then ok but it will replace ALL duplicated values
post more representative example

Not sure if this image helps. I want to go through all the ID in the column and if we thats its a duplicate then go to the units column and delete that duplicate. If i do what you are asking i would delete all duplicates in the units column right?

 

[IML_56]

something like this?

Project	Funding	Boxes		Project	Funding	Custom
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
zz	89	30		zz	89
vv	345	30		vv	345
dd	543	125		dd	543	125

Yes exactly like that. Is it possible to do that?

 

[mumps]

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

 

[sandy666]

If i do what you are asking i would delete all duplicates in the units column right?

Right

Yes exactly like that. Is it possible to do that?

sure
it is a big table, how many records are there? Maybe manually will be faster ?

 

[sandy666]

I c you got solution