comparing columns

[harken]

I would appreciate some assistance in coding a subroutine to do the following: Given two columns (a and b) with differing numbers of text entries, I?d like to compare the two and have the following results placed in three other columns ? entries that are in col A, but not in col B; entries in B, but not in A; and common entries.

Thanks for any help

[shamsam1]

this will help


Sub CompareColumns()


Dim Column1 As Range
Dim Column2 As Range

'Prompt user for the first column range to compare...
'----------------------------------------------------
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)

'Check that the range they have provided consists of only 1 column...
If Column1.Columns.Count > 1 Then

Do Until Column1.Columns.Count = 1

MsgBox "You can only select 1 column"
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)

Loop

End If

'Prompt user for the second column range to compare...
'----------------------------------------------------
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)

'Check that the range they have provided consists of only 1 column...
If Column2.Columns.Count > 1 Then

Do Until Column2.Columns.Count = 1

MsgBox "You can only select 1 column"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)

Loop

End If


'Check both column ranges are the same size...
'---------------------------------------------
If Column2.Rows.Count <> Column1.Rows.Count Then

Do Until Column2.Rows.Count = Column1.Rows.Count

MsgBox "The second column must be the same size as the first"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)

Loop

End If

'If entire columns have been selected (e.g. $Ahttp://forums.microsoft.com/MSDN/emoticons/emotion-10.gifA), limit the range sizes to the
'UsedRange of the active sheet. This stops the routine checking the entire sheet
'unnecessarily.
'-------------------------------------------------------------------------------
If Column1.Rows.Count = 65536 Then

Set Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))
Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))

End If


'Perform the comparison and set cells that are the same to yellow
'----------------------------------------------------------------
Dim intCell As Long

For intCell = 1 To Column1.Rows.Count

If Column1.Cells(intCell) = Column2.Cells(intCell) Then

Column1.Cells(intCell).Interior.Color = vbYellow
Column2.Cells(intCell).Interior.Color = vbYellow

End If

Next

End Sub

[shamsam1]

http://www.vb-helper.com/howto_excel_compare_ranges.html

if ur playing with numbers this will be good

[harken]

The columns are different sizes...the following code does return the common values, but if I change the = operator to <> in the "if x = y" stmt, it returns all the values in column C rather than those that are different. I can't figure out why it is doing this. Can anyone help/ If it makes any difference, the values are text, not numbers.


Dim CompareRange As Variant, x As Variant, y As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange = Range("C5:C200")
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.
' Set CompareRange = Workbooks("Book2"). _
' Worksheets("Sheet2").Range("C1:C5")
'
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

Range("a5:a200").Select
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x
Next y
Next x