excel - Searching for data that is not present -

i have 2 sets of data ranges. 1 range in sheet 1 (8000 rows, 6 columns) master range , other range in sheet 2 (5000 rows, 6 columns). how can display other 3000 rows on new sheet (sheet 3) not in sheet 2 in sheet 1?

i can make macro, first, let me know if you're looking for.

here's sample data used, in 3 sheets.

sheet 1 called "master" following, starting in a1:

name            birthday month     age batman          january            35 superman        march              32 catwoman        april              37 joker           june               28 harley quinn    september          33 rorschach       july               35 dr. manhattan   august             41 aquaman         october            22 ms. jupiter     october            23 penguin         july               39 

and in sheet2:

name            birthday month     age batman          january            35 superman        march              32 catwoman        april              37 joker           june               28 harley quinn    september          33 rorschach       july               35 

so, can see, need dr. manhattan on down added (currently blank) sheet3.

a quick way check see not in sheet 2, add helper column master sheet, , count number of times each record shows in sheet2. on master sheet, enter formula d2 , copy down, =countifs(sheet2!$a:$a,a2,sheet2!$b:$b,b2,sheet2!$c:$c,c2).

this leave count of times record shows in sheet 2. see "0", means need add records sheet3:

and there go.

note: since have huge amount of rows, alternating 1's , 0's. determine haven't been included in sheet2, sort data column d ("with headers"):

(note: selected range a1:d11, since chose "has headers", excel drops visibly selected range 1 row. hit "ok" , "0" reports @ top, these can add sheet 3.

i can make macro, if kindly let me know how works first. thanks!

edit: macro included, please let me know if need tweaks!

  sub find_reports()     dim lastrow long, lastcol integer, missinglastrow integer     ' create variables master worksheet, list wsht (which has of master records), , missing ws put missing records     dim masterws worksheet, listws worksheet, missingws worksheet     dim listwsname string dim ws worksheet  application.displayalerts= false each ws in sheets      if ws.name = "missing" ws.delete next ws application.displayalerts= true      set masterws = sheets("master list") ' change whatever master list sheet called     set listws = sheets("sheet2") ' same master, change whatever second sheet (that has of data) called     listwsname = listws.name      sheets.add.name = "missing"     set missingws = sheets("missing")     missingws.rows(1).value = masterws.rows(1).value     missinglastrow = 2 'start on row 2      lastrow = masterws.cells(1, 1).end(xldown).row 'i assuming have no gaps in column a, if do, uncomment , use next line instead     'lastrow = masterws.usedrange.rows.count      lastcol = masterws.cells(1, 1).end(xltoright).column      dim countcol integer     countcol = lastcol + 1 'we add count formula column     masterws.cells(1, countcol).value = "times included in " & listwsname     masterws         .range(.cells(2, countcol), .cells(lastrow, countcol)).formular1c1 = "=countifs(" & listwsname & "!c[-3],rc[-3]," & listwsname & "!c[-2],rc[-2]," & listwsname & "!c[-1],rc[-1])"     end      'now, add rows "0" next in master ws new "missing" ws     dim integer     masterws         = 2 lastrow             if .cells(i, countcol).value = 0 'if there no record, add missing ws                 missingws.range(missingws.cells(missinglastrow, 1), missingws.cells(missinglastrow, lastcol)).value = _                     .range(.cells(i, 1), .cells(i, lastcol)).value                 missinglastrow = missinglastrow + 1             end if         next     end       end sub