Comparing values in 2 columns - Help needed

Hi There

Since I am quite new to Excel I am having some problems with comparing values listet in two columns.
I need to compare the values in column B (lets say B1-B88) with the values in A (A1 to A88). If the value in B1 (and so on) match any value in A1-A88 it must be deleted from both columns(want to avoid double count - some of the values may be listed more than once).
It sounds like a simple problem but I cant make it work.
I hope someone is able to help...Thanks

All the best

Tilde

Backup your workbook before trying this macro

try this macro and let me know

Sub macro()
Dim r, c As Range
Dim i, k As Variant
Range("b1:b88").Select
Set r = Selection
k = 88
For i = 1 To 88
If Range("b" & i).Value <> "" Then
temp = check_value(Range("b" & i).Value)

If temp = 1 Then
Range("b" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
k = k - 1
Else

End If
End If
Next

End Sub

Function check_value(val As Variant)
Range("a1").Select
Range("a1:a88").Select
On Error Resume Next
Selection.Find(What:=val, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Range(ActiveCell.Address).Select
Selection.Delete Shift:=xlUp
If Err.Description <> "" Then
check_value = 0
Else
check_value = 1
End If
End Function

Not sure this approach meets your needs, but this places a 1 in the third
column when the value in column B is found in column A.
The 1 can then be used to remove the duplicate entry (method not shonw).
But I am not sure about your requirement "deleted from both columns" - if
the objective is to remove entries from column A as duplicates also
appearing in column B, why do you want to remove column B entries as surely
this is the reference list that determines what to detect in A? Maybe I am
missing something(?).

Sub DeDupe()
Dim x As Long, y As Long, v As Variant
For x = 1 To 10
For y = 1 To 10
If Cells(x, 2) = Cells(y, 1) Then Cells(y, 3) = 1
Next y
Next x
End Sub

--
Cheers
Nigel



"tilde" <tilde.1qss2a_1119056702.5808@excelforum-nospam.com> wrote in
message news:tilde.1qss2a_1119056702.5808@excelforum-nospam.com...
>
> Hi There
>
> Since I am quite new to Excel I am having some problems with comparing
> values listet in two columns.
> I need to compare the values in column B (lets say B1-B88) with the
> values in A (A1 to A88). If the value in B1 (and so on) match any value
> in A1-A88 it must be deleted from both columns(want to avoid double
> count - some of the values may be listed more than once).
> It sounds like a simple problem but I cant make it work.
> I hope someone is able to help...Thanks
>
> All the best
>
> Tilde
>
>
> --
> tilde
> ------------------------------------------------------------------------
> tilde's Profile:
http://www.excelforum.com/member.php...o&userid=24424
> View this thread: http://www.excelforum.com/showthread...hreadid=380264
>

Thanks for your answer.....It gives me something to work with. It doesnt work properly but I will used your code and work on it

Originally Posted by anilsolipuram

Backup your workbook before trying this macro

try this macro and let me know

Sub macro()
Dim r, c As Range
Dim i, k As Variant
Range("b1:b88").Select
Set r = Selection
k = 88
For i = 1 To 88
If Range("b" & i).Value <> "" Then
temp = check_value(Range("b" & i).Value)

If temp = 1 Then
Range("b" & i).Select
Selection.Delete Shift:=xlUp
i = i - 1
k = k - 1
Else

End If
End If
Next

End Sub

Function check_value(val As Variant)
Range("a1").Select
Range("a1:a88").Select
On Error Resume Next
Selection.Find(What:=val, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Range(ActiveCell.Address).Select
Selection.Delete Shift:=xlUp
If Err.Description <> "" Then
check_value = 0
Else
check_value = 1
End If
End Function