I've tried searching for a solution without success (probably using th
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 b
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

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=49382

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" wrote in message
...

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

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,

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" wrote in message
...

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

Cheers, I'll get right on it :

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=49382

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,

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" wrote in message
...

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

Excellent. A new thing to try out :)

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

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=49382

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.