Comparing values in 2 different ranges

[Todd Huttenstine]

Hey guys

I have 2 ranges: A:A and B:B. Each and every value in
A:A will be somewhere in range B:B. However each and
every value in B:B may NOT be in range A:A. I need a code
that will look in these 2 ranges and find all the values
that are listed in range B:B but not in range A:A and list
all those value in range C:C.

For example: Range A:A contains the following 5
values... "A", "B", "C", "D", "E". Range B:B contains the
following 5 values... "A", "C", "d", "e", "F".

When the code is run range C:C needs to contain only the
value "F" because "F" was found in range B:B but not in
range A:A.

How would this be done?

Thank you
Todd Huttenstine

 

[Bob Phillips]

Worksheet formula

C1: =IF(COUNTIF(A:A,B1)>0,"",B1)

and copy down

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Trevor Shuttleworth]

Todd

one way:

Sub ListUniques()

Dim LastRow As Long
Dim i As Long
Dim j As Long

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Columns("C").Clear
For i = 1 To LastRow
If WorksheetFunction.CountIf(Range("A:A"), Range("B" & i)) = 0 Then
j = j + 1
Range("C" & j) = Range("B" & i)
End If
Next 'i
End Sub

Regards

Trevor

 

[Todd Huttenstine]

How would you do it in a loop to where its
programmatically puts the missing values in column C so
that there are no cells with "" in them?


Thank you

 

[Bob Phillips]

Dim i As Long
Dim iNextRow As Long

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Application.Evaluate("CountIf(A:A,B" & i & ")") = 0 Then
iNextRow = iNextRow + 1
Cells(iNextRow, "C").Value = Cells(i, "B").Value
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

Thanx how do I get it to start listing in C2?

-----Original Message-----
Todd

one way:

Sub ListUniques()

Dim LastRow As Long
Dim i As Long
Dim j As Long

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Columns("C").Clear
For i = 1 To LastRow
If WorksheetFunction.CountIf(Range("A:A"), Range("B" & i)) = 0 Then
j = j + 1
Range("C" & j) = Range("B" & i)
End If
Next 'i
End Sub

Regards

Trevor





.

 

[Trevor Shuttleworth]

Todd

presumably you want to check from row 2 as well ?

Sub ListUniques()

Dim LastRow As Long
Dim i As Long
Dim j As Long

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Columns("C").Clear
j = 1 ' this is new
For i = 2 To LastRow ' this has changed from 1 to 2
If WorksheetFunction.CountIf(Range("A:A"), Range("B" & i)) = 0 Then
j = j + 1
Range("C" & j) = Range("B" & i)
End If
Next 'i
End Sub

Regards

Trevor