Deleting cells in a column ref a different column

[Daminc]

I've tried searching for a solution without success (probably using the wrong key phrases)

I have a numbers in column A (4434)
And I have numbers in column C (389)

I wish for any number in column C that is duplicated in column A to be deleted from column A.

I've tried messing around with:

Sub rem_dup_test()
'
' rem_dup_test Macro
' Macro recorded 15/12/2005 by PreeceJ
'

'
Application.ScreenUpdating = False


Dim lastrow As Long, i As Long, l As Long
Dim DupNum As Range, MainColumn As Range

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
For l = 1 To lastrow

Set DupNum = Cells(i, "C")
Set MainColumn = Cells(i, "A")
If MainColumn = DupNum Then Selection.Delete Shift:=xlUp
Next l
Next i

End Sub

but it doesn't even come close

Any advice please?

[Bernie Deitrick]

Daminc,

Try the macro below. Assumes that your header is in row 1, and there are no blanks in column A.

HTH,
Bernie
MS Excel MVP


Sub DeleteRepeatsInColumnABasedOnColumnC()
Dim myRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

myRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").EntireColumn.Insert
Range("A1").Value = "Flag"
Range("A2").Formula = _
"=IF(COUNTIF(D:D,B2)>0,""Delete"","""")"
Range("A2").AutoFill Destination:=Range("A2:A" & myRow)
Range("A:B").Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes
With Range("A:A")
.AutoFilter Field:=1, Criteria1:="Delete"
.SpecialCells(xlCellTypeVisible).Areas(2).Select
Selection.Offset(0, 1).Select
Selection.Delete Shift:=xlUp
.EntireColumn.Delete
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


"Daminc" <[email protected]> wrote in message
news:[email protected]...
>
> I've tried searching for a solution without success (probably using the
> wrong key phrases)
>
> I have a numbers in column A (4434)
> And I have numbers in column C (389)
>
> I wish for any number in column C that is duplicated in column A to be
> deleted from column A.
>
> I've tried messing around with:
>
> Sub rem_dup_test()
> '
> ' rem_dup_test Macro
> ' Macro recorded 15/12/2005 by PreeceJ
> '
>
> '
> Application.ScreenUpdating = False
>
>
> Dim lastrow As Long, i As Long, l As Long
> Dim DupNum As Range, MainColumn As Range
>
> lastrow = Cells(Rows.Count, 1).End(xlUp).Row
> For i = 1 To lastrow
> For l = 1 To lastrow
>
> Set DupNum = Cells(i, "C")
> Set MainColumn = Cells(i, "A")
> If MainColumn = DupNum Then Selection.Delete Shift:=xlUp
> Next l
> Next i
>
> End Sub
>
> but it doesn't even come close
>
> Any advice please?
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=493823
>

[Daminc]

Cheers Bernie, it seems like it works but at the moment the math doesn't work out.

Original: 4434
Dup: 389

Anticipated result: 4045
Actual result: 4082

which looks like 37 of the duplicates weren't duplicates.

I'll have to do a manual check to validate this.

It would help if I understood your macro.
98% of it I haven't come across before

[Bernie Deitrick]

Daminc,

The macro simply inserts a new column, adds COUNTIF formulas to flag duplicates to delete, and then
sorts and deletes the duplicates, then deletes the inserted column.

If you want to see what is happening, use 2 macros: one that puts in the formula first, and a second
that does the deletion. That way, you can see the flagging and check why numbers you think are
duplicates aren't actually duplicates.

See the two macros below.

HTH,
Bernie
MS Excel MVP

'First Macro
Sub ShowDuplicates()
Dim myRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

myRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").EntireColumn.Insert
Range("A1").Value = "Flag"
Range("A2").Formula = _
"=IF(COUNTIF(D:D,B2)>0,""Duplicate"","""")"
Range("A2").AutoFill Destination:=Range("A2:A" & myRow)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

'Second macro
Sub DeleteDuplicates()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Range("A:B").Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes
With Range("A:A")
.AutoFilter Field:=1, Criteria1:="Duplicate"
.SpecialCells(xlCellTypeVisible).Areas(2).Select
Selection.Offset(0, 1).Select
Selection.Delete Shift:=xlUp
.EntireColumn.Delete
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub




"Daminc" <[email protected]> wrote in message
news:[email protected]...
>
> Cheers Bernie, it seems like it works but at the moment the math doesn't
> work out.
>
> Original: 4434
> Dup: 389
>
> Anticipated result: 4045
> Actual result: 4082
>
> which looks like 37 of the duplicates weren't duplicates.
>
> I'll have to do a manual check to validate this.
>
> It would help if I understood your macro.
> 98% of it I haven't come across before
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=493823
>

[Daminc]

Cheers, I'll get right on it

[Daminc]

Just an update Bernie.

Spliting up the macro worked a treat. It solved my problem as well as help me understand the coding a little better.

I'm thinking of ways to adapt it into an error trap to prevent non-duplicates appearing in the duplicate list which should help my colleague somewhat )

Cheers again.

[Bernie Deitrick]

Daminc,

If you want to prevent duplicates from being entered, you can use Data Validation with a custom
function. For example, to prevent entering a value into column A that already appears in column C,
select all of column A, select Data / Validation.... Settings Tab, under "Allow" select Custom,
and enter this in the formula area:

=COUNTIF(C:C,A1)=0

You can modify your error message to inform the user what s/he is doing wrong....

HTH,
Bernie
MS Excel MVP


"Daminc" <[email protected]> wrote in message
news:[email protected]...
>
> Just an update Bernie.
>
> Spliting up the macro worked a treat. It solved my problem as well as
> help me understand the coding a little better.
>
> I'm thinking of ways to adapt it into an error trap to prevent
> non-duplicates appearing in the duplicate list which should help my
> colleague somewhat )
>
> Cheers again.
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=493823
>

[Daminc]

Excellent. A new thing to try out

Have a great Christmas and a Happy New Year. Ho ho ho.

[Bernie Deitrick]

Ho, ho, ho indeed... Thanks.

Bernie
MS Excel MVP

> Excellent. A new thing to try out
>
> Have a great Christmas and a Happy New Year. Ho ho ho.