compare columns

Its doing the opposite i want it do.  May i send you  a spreadsheet with actual data.  Sometimes its a better look at it.  I want the results sheet to show me the rows that didn't match with ws2's info..

jay.rueschman@navy.mil is my address.  If you wanna send me your email

Hi Jay:

You can attach the file to a comment.  Just check the attach file check box & provide the path.

Bob

lynx20,
The search criteria sheet should compare to the sample data sheet.  Any records that do not match needs to be stuffed into the results sheet.  Right now its putting the records that do match into the results sheet.

The macro works as written.  You have a text vs number issue going on.

To see what is going on, select cell A1 on the data page.  Click in the cell to edit the value & hit enter.  Do the same on the search page.  Now select A6 on the search page, start an edit & hit enter.  Notice how it shifted to the right.  The value in this cell is a number and not text.  Just select all the cells to A190 & format as text.  Now the macro runs as desired.

Bob

Lynx20,
Thanks for the tip on the text.  The macro is working as written yes however it's not written correctly for what I want it to do.  Run the macro and copy one of the values from the results sheet then go to the data sheet and do a find on the first column and it will find that value.  It should not find it because i want results sheet to show the records that dont match the data sheet.   If it does not find the value it will copy the row from ws2 that was used to do the compare to the results sheet.  Sorry if i did not make myself clear.

After further looking at things, some values are still being interperted as numbers and not text.  So I modified the macro to force all values in column A to be text.

I hope this solves your problem.

Bob

Sub CompareSheets2()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim RowsA As Long, RowsB As Long, i As Long, j As Long, counter As Long
Dim strData As String
Dim strSerch As String
Dim blnFound As Boolean
 
blnFound = False
 
Set ws1 = Sheets("SampleData") ' your sample data sheet
Set ws2 = Sheets("Search criteria") ' your sheetname
Set ws3 = Sheets("Results") ' your sheetname
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
RowsA = ws1.Cells(Rows.Count, 1).End(xlUp).Row
RowsB = ws2.Cells(Rows.Count, 1).End(xlUp).Row
counter = 1
 
'copy values
For j = 1 To RowsB
  For i = 1 To RowsA - 1
    strData = CStr(ws1.Cells(i, 1).Value)
    strSearch = CStr(ws2.Cells(j, 1).Value)
    If strData = strSearch Then
      blnFound = True
      Exit For
    End If
  Next i
  If Not blnFound Then
      ws2.Rows(j).EntireRow.Copy ws3.Cells(counter, 1)
      ws3.Cells(counter, 4) = j & " - " & i
      counter = counter + 1
    End If
  blnFound = False
Next j
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

Actually to clarify: I only forced the falues to be text in the comparison.  No cells are being modified on the data or search page.

Bob

lynx20,
Sorry but is still not working correctly.  Its very close tho.  If you put the below in both the sample data sheet and in the search sheet the results will show data and should come up blank because the search was a exact match.  Its also for me putting a number like 3-3  in the result and sometimes a date.  Have no clue how thas happening.

1      xx      xx
2      xx      xx
3      xx      xx

My mistake here, I copied from the wrong version on my computer.

Replace the 2nd For to be:    For i = 1 To RowsA

Bob

Lynx20,
We are almost there.  That fixed most of the problem.  Look at the results sheet on the attached.  I have now clue where these numbers are coming from in column c.  If you can figure that out we are done.  Thanks for helping me on this.