Hi Jill
Firstly, my post assumed your lists started in range A2 and B2 and there
were column headings in A1 and B1. I retested with my formula and the
resultant list in Column C did not contain any zeros.
As an alternative, the following is an adaptation of code to return unique
items from a given list. I no longer have the author's name so my apologies
for not having given the deserving credit to the author.
The code assumes your two lists are in Columns A and B and the list in
Column A starts from A2.
Sub Test()
Dim r As Range, c As Range, cl As New Collection, it As Variant
Dim p As String, p2 As String, i As Integer, ii As Integer
Range("C:C").ClearContents
'Amend range as necessary
Set r = Range("A2:A37")
On Error Resume Next
For Each c In r
cl.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
For i = 1 To cl.Count - 1
For ii = i + 1 To cl.Count
If cl(i) > cl(ii) Then
p = cl(i)
p2 = cl(ii)
cl.Add p, before:=ii
cl.Add p2, before:=i
cl.Remove i + 1
cl.Remove ii + 1
End If
Next ii
Next i
For Each it In cl
If Application.CountIf(Range("B:B"), it) = 0 Then _
Range("C65000").End(xlUp).Offset(1, 0) = it
Next it
End Sub
--
XL2002
Regards
William
(e-mail address removed)
| William, thanks for the reply but that isn't what I had in
| mind. That still requires at least 2 steps and besides the
| formula doesn't work properly. That formula returned:
|
| Tom
|
| Sam
| Jill
|
| 0
|
|
| Thanks for your time and effort.
| Jill
| >-----Original Message-----
| >Jill
| >
| >With your first list in Column A and your second list in
| Column B, enter the
| >following in C2 and drag down. Adjust range to suit.
| >=IF(COUNTIF($B$2:$B$27,A2)+COUNTIF($C$1:C1,A2)=0,A2,"")
| >[You may want to then convert column C list to values and
| then sort]
| >
| >--
| >XL2002
| >Regards
| >
| >William
| >
| >
[email protected]
| >
| message
| >| >| Hello!
| >|
| >| I have two columns of names:
| >|
| >| Jim Tom
| >| Tom Bob
| >| Eric Sue
| >| Sam Jim
| >| Jill Bill
| >| Sue Eric
| >|
| >| I want to extract the names in the first column that do
| >| not appear in the second column. I can do this using a
| >| helper column in 2 steps but I would like to know how to
| >| do it without using a helper column and in one step
| >| (formula please!).
| >|
| >| The output would look like this in the third column:
| >|
| >| Jim Tom Sam
| >| Tom Bob Jill
| >| Eric Sue
| >| Sam Jim
| >| Jill Bill
| >| Sue Eric
| >|
| >| Thanks!
| >|
| >| Jill
| >
| >
| >
| >
| >
| >.
| >
