excel - Condense many tables with overlapping information into one table -

i have excel workbook 15 worksheets. each worksheet contains 1 table list of species. there quite bit of overlap of species among worksheets. i'm trying reduce overlap merging table.

i need condense 15 worksheets 1 table lists of species 1 time, , shows if each species present in worksheet x. first column of species, column names each worksheet, , body of table check marks indicating if species appeared in worksheet x. here example:

worksheet 1                             worksheet 2 species                 species code    species                species code casurina equisitifolia  caeq            odocoilius virginianus odvi columbrina asiatica     coas            gavia immer            gaim gavia immer             gaim              table want create species                 worksheet 1 worksheet 2 casurina equisitifolia  y           n columbrina asiatica     y           n gavia immer             y           y odocoilius virginianus  n           y`` 

there different ways achieve this. below 1 approach:

first create new worksheet (referenced sheet1 example) in workbook. add names in column "a". add sheet name header in respective columns. add below formula in cell "b2":

=if(iserror(match(sheet1!a2,sheet2!$a:$a,0)),"n","y") 

where sheet1!a2 holds name looking for. sheet2!$a:$a first sheet holds names (in column "a" of sheet2 example). give "y" if name exist or "n" if doesn't exist. drag formula cell down length of names , give need sheet 2. repeat process each of sheets have , should have need. hope helps