excel - Find value intersections in multiple columns -

i out of depth here: can done? , if so, methods should consider?

i periodically receive spreadsheet contains variable number of sheets. each sheet has same header row, different values in rows beneath. in 1 column identifying number indicates unique user, , need determine if there intersection between of identifier columns on worksheets. here simplified example, in first , third worksheet have intersection of abc789 there no intersecting value in worksheet 2. want know when there intersection, , between worksheets:

 worksheet 1:  id_number •   abc123 •   abc456 •   abc789  worksheet 2:  id_number •   abc234 •   abc345 •   abc912  worksheet 3:  id_number •   abc789 •   abc567 •   abc678

if can done, i'm suspicious of problem: doing in way works 3 sheets today , 10 sheets tomorrow! answer question tried setting variables unknown number of columns compare this, failed:

dim iarraysize integer dim itabcounter integer dim iloopcounter integer  itabcounter = activeworkbook.sheets.count  iloopcounter = 3 itabcounter     iarraysize = activeworkbook.sheets(iloopcounter).range("c2", range("c2").end(xldown)).count     dim aid & iloopcounter variant 'this line fails on compile "expected end of statement" highlighting ampersand     aid1 = range("c2", range("c2").end(xldown)).value next iloopcounter 

is lost cause? should resolve myself manual examination?

this output list of id's found more once , sheets found in on summary sheet:

sub tgr()      const stridcol string = "a"     const lheaderrow long = 1      dim cllids collection     dim ws worksheet     dim idcell range     dim arrunqids(1 65000) variant     dim arrmatches(1 65000) string     dim resultindex long     dim lunqidcount long      set cllids = new collection      each ws in activeworkbook.sheets         range(ws.cells(lheaderrow + 1, stridcol), ws.cells(ws.rows.count, stridcol).end(xlup))             if .row > lheaderrow                 each idcell in .cells                     on error resume next                     cllids.add idcell.text, lcase(idcell.text)                     on error goto 0                     if cllids.count > lunqidcount                         lunqidcount = cllids.count                         arrunqids(lunqidcount) = idcell.text                         arrmatches(lunqidcount) = ws.name                     else                         resultindex = worksheetfunction.match(idcell.text, arrunqids, 0)                         arrmatches(resultindex) = arrmatches(resultindex) & "|" & ws.name                     end if                 next idcell             end if         end     next ws      if lunqidcount > 0         sheets.add(before:=activeworkbook.sheets(1))             .range("a1:b1")                 .value = array("intersecting id's", "intersected in sheets...")                 .font.bold = true             end             .range("a2").resize(lunqidcount).value = application.transpose(arrunqids)             .range("b2").resize(lunqidcount).value = application.transpose(arrmatches)             .usedrange.autofilter 2, "<>*|*"             .usedrange.offset(1).entirerow.delete             .usedrange.autofilter             .range("a1").currentregion.entirecolumn.autofit         end     end if      set cllids = nothing     set ws = nothing     set idcell = nothing     erase arrunqids     erase arrmatches  end sub