Comparing column A to column C

[mcgerks]

Hi,

I would like to compare column A with column C. If a string in column A is not in column C. I would like to display the string in column D. The data is not sorted. I found a way to do this but it take TEN MINUTES of processing time to complete column D. This is the code I used:

Sub Button2_Click()

'put all the data in Column D

For i = 1 To 2440
Range("d" & i).Value = Range("a" & i).Value 'put all the data in Column D
Next i

'Erase the data that is the same in column A and column C

For i = 1 To 2440
For j = 1 To 2440
If Range("a" & i).Value = Range("c" & j).Value Then
Range("d" & i).Value = " "
End If
Next j
Next i
End Sub

I would also like to get ride of all the spaces in column D that the data was erased from. I tried this:

For i = 1 To 2440
If Range("d" & i).Value = Empty Then
Range("d" & i).Select
Selection.Delete Shift:=xlUp
End If
Next i
End Sub

But this only work if there is one between data not with two or more spaces.

I put the sheet on the internet at

http://www.geocities.com/mcgerks/test3.xls


Thank you for your help

- Keith

[crimlet]

I have tried to look at your data, but the server says that URL is not avalible. Any way try putting the formula =if(a1<>b1,A1,"")and fill down then just sort by the column you put the data in and the blanks will either appear at the top or the bottom depending on which sort option you chose

[mcgerks]

I not sure if this will work because there might not be the same data in A1 as in C1 but the data in A1 might be same in C43, and I don't want dupicate data. Thats why I had to make a nested for loop.

Thanks

Keith