2 Columns - Find and extract non-common numbers?

[cc_web]

Basically I have two columns of numbers. Column A refers to an old set of numbers and Column B refers to the new set. I need to go through and find/extract the numbers from column A that do not apear in Column B and vice versa.

Note: Both columns A & B are results of imported data so the values/positioning those changes every time i update the imported files.

I need to extract these values to two different sheets. The numbers that do NOT appear in Column B but DO appear in Column A need to simply be outputted to sheet for visual reference (they will manually be deleted from a datbase later). The Numbers in Column B that do NOT appear in Column A need to also be outputted to a separate worksheet, but I would like the values to appear one after another rather than being spaced out by empty rows ect.. These values will be further used to lookup data from another imported file.

Got all that, lol? I have experimented with various formulas but I can't seem to get anything to work out just right.

Your help would be most appreciated.

 

[iggydarsa]

Do you mean something like this:

Sub macro1()
    ALastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    BLastRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
    S2LastRow = Worksheets("Sheet2").Range("A65536").End(xlUp).Row
    S3LastRow = Worksheets("Sheet3").Range("A65536").End(xlUp).Row
    
    found = False
    
    For i = 1 To ALastRow
        For j = 1 To BLastRow And Not found
            If Cells(i, 1).Value = Cells(j, 2).Value Then
                found = True
            End If
        Next j
        
        If j = BLastRow + 1 And Not found Then
            Worksheets("Sheet2").Range("A" & S2LastRow) = Cells(i, 1)
            S2LastRow = S2LastRow + 1
        End If
        
        found = False
    Next i
    
    found = False
    For i = 1 To BLastRow
        For j = 1 To ALastRow And Not found
            If Cells(i, 2) = Cells(j, 1) Then
                found = True
            End If
        Next j

        If j = ALastRow + 1 And Not found Then
            Worksheets("Sheet3").Range("A" & S3LastRow) = Cells(i, 2)
            S3LastRow = S3LastRow + 1
        End If

        found = False
    Next i
    
End Sub

It assumes that your 2 columns are A and B and in Sheet1, the differences on Col A is stored on Sheet2 and the differences on Col B is stored on Sheet3...

 

[cc_web]

Thanks iggydarsa for your reply. I guess I should have mentioned that i am a newb and not sure what to do with what you have posted.

I imagine this is a macro, but i am not familar with using this. Your help is appreciated.

Thanks

 

[iggydarsa]

Open the file you wanna work on... press Alt+F11... VB Editor will open...
Goto INSERT -> Module... paste the code on the main window (white background)... and when you want it to run, goto spreadsheet click on Tool->Macro->Macros and Run macro1...

make sure that your sheet names are Sheet1, Sheet2, Sheet3... if you have other sheet names, replace the "Sheet1" "Sheet2" and "Sheet3" with your sheet names...

good luck

 

[cc_web]

Thanks again iggydarsa...

At first I wasn't waiting long enough but it worked like a charm.

Thanks a bunch.

 

[iggydarsa]

np