# 2 Columns - Find and extract non-common numbers?

#### cc_web

##### New Member
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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Do you mean something like this:

Code:
``````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
If Cells(i, 1).Value = Cells(j, 2).Value Then
found = True
End If
Next j

Worksheets("Sheet2").Range("A" & S2LastRow) = Cells(i, 1)
S2LastRow = S2LastRow + 1
End If

found = False
Next i

found = False
For i = 1 To BLastRow
If Cells(i, 2) = Cells(j, 1) Then
found = True
End If
Next j

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...

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

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

Thanks again iggydarsa...

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

Thanks a bunch.

np

Replies
5
Views
145
Replies
6
Views
301
Replies
7
Views
361
Replies
5
Views
454
Replies
5
Views
584

1,203,030
Messages
6,053,129
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back