excel - Comparing two sheets - difference write to sheet 3 -

here thing: have 2 sheets (approx 10k rows in both) - compare them - if there match (based on column b - nothing, if there isn´t - value in sheet2 not in sheet 1 - write columns(a,b,c,d) sheet3) - problem is, first row in sheet 1 equal maybe 3rd row in sheet2.

how can achieve that?

is possible check row row according values in column b?

thanks

i have created workbook following criteria:

sheet1:

column  | column b | column c |  column d | column e ------------------------------------------------------ 111024       961207    value1       data     fake 11 111027       961209    value2       data b     fake 22 111030       961211    value3       data     fake 33 ... ... ... 

and sheet 2 copy of sheet 1 couple of rows missing.

then opened visual basic editor (alt+f11) , added module, wrote following macro:

sub compare()     sheets(3).activate  'go sheet 3     cells.clear         'and clear previous results      range("a1").select  'set cursor @ top      sheets(1).activate  'go sheet 1     range("a1").select  'begin @ top        dim search_for string   'temp variable hold need     dim cnt integer         'optional counter find out how many rows found      while activecell.value <> ""   'repeat follwoing loop until reaches blank row          search_for = activecell.offset(0, 1).value   'get hold of value in column b          sheets(2).activate  'go sheet(2)          on error resume next   'incase search not found, no errors stop macro          range("b:b").find(search_for).select  'find value in column b of sheet 2          if err <> 0   'if value not found, err not 0              on error goto 0  'clearing error code              sheets(1).activate   'go sheet 1              r = activecell.row   'get hold of current row index              range(r & ":" & r).select  'select whole row              cnt = cnt + 1   'increment counter              selection.copy  'copy current selection              sheets(3).activate  'go sheet 3              activecell.pastespecial xlpasteall  'past entire row sheet 3              activecell.offset(1, 0).select  'go down 1 row prepare next row.           end if         sheets(1).activate   'return sheet 1         activecell.offset(1, 0).select   'go next row      loop   'repeat      sheets(3).activate    'go sheet 3 examine findings      msgbox "i have found " & cnt & " rows did not exist in sheet 2"  end sub 

then ran macro , found working..

i hope answer helped achieve want.

if want, here excel workbook created need enable macro before see code run. office automatically warn of excel file contain macros.