Data comparison

jyokom

Board Regular
Joined
May 24, 2004
Messages
148
I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
If your columns have headers, Advanced Filter will do that.
If column E is blank,
leave E1 blank and put =ISNA(MATCH(B2,A:A,0)) in E2.

AdvancedFilter with the Copy to another location option and the
List range = $B$1:$B100
Criteria range = $E$1:$E$2
Copy to range = $C$1

will do what you want.
 
Upvote 0
This code will move Unique items in Column B to Column C.
Code:
Sub MoveUnique()
'Move items in column B that are not in column A to column C
    lastrow = ActiveSheet.UsedRange.Rows.Count
    For i = 1 To lastrow
         If Not Application.WorksheetFunction.CountIf(Range("A:A"), Range("B" & i)) = 1 Then
              Range("B" & i).Cut Range("C" & i)
         End If
    Next i
End Sub
 
Upvote 0
I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.
I tried =IF(VLOOKUP(A1,TKACT1:TKACT90,1)=A1,0,B1) This doesn't work.
 
Upvote 0
If you just want to copy instead of Move, use this code:
Code:
Sub CopyB2C()
'Copy items in column B that are not in column A to column C
    lastrow = ActiveSheet.UsedRange.Rows.Count
    For i = 1 To lastrow
         If Not Application.WorksheetFunction.CountIf(Range("A:A"), Range("B" & i)) = 1 Then
              Range("B" & i).Copy Range("C" & i)
         End If
    Next i
End Sub
To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macros start with "Sub MacroName()" and End with "End Sub"

How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialog box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.
 
Upvote 0
Datsmart,

With some modification, that works great. One slight thing. When The macro runs, it puts the data in the same row as the data in "B". How do I get it to start at the top of column C and skip the blanks?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top