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


--
tilde
------------------------------------------------------------------------
tilde's Profile: http://www.excelforum.com/member.php...o&userid=24424
View this thread: http://www.excelforum.com/showthread...hreadid=380264

Comparing values in 2 columns - Help needed
 

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380264

Comparing values in 2 columns - Help needed
 

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

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

Comparing values in 2 columns - Help needed
 

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 :)



anilsolipuram Wrote:
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


--
tilde
------------------------------------------------------------------------
tilde's Profile: http://www.excelforum.com/member.php...o&userid=24424
View this thread: http://www.excelforum.com/showthread...hreadid=380264