excel - Conditional copy/paste cells from sheet to sheet based on overlapping content in A columns? -

excel 2011, mac.

two sheets, both names in "last, first" descending in a. name list of sheet 1 shorter name list on sheet 2. sheet 2 has headings there in row 1, rest of cells in 12 columns have been left blank.

i need 12 columns of data names in sheet 1 right rows of sheet 2.

i tied use pivot table, pivot brought on 0s , 1s instead of actual data in sheet1, though did bring on headings. used pivot (copy/paste) create normal sheet copying task.

i tried figure out way code makes sense, fell on face because cant figure out how go without making overly complicated.

i add rows between matching names until matches a, copy , paste whole block of cells @ once. find matches , copy data in sheet2 go. build list(array?) of matches go through , copy each match's stuff over... ugh. know these kludgy ideas, can't find better in searches - or @ least search terms useless when hunting them.

so not asking all code, asking 2 things:

1) code efficiently comparison between names in both sheets' columns.

2) suggestions on logical operations process fastest code (the least figure out me, is).

eg:

underscore indicates empty cell.  sheet 1          sheet 2       b   c...         b  c  d... 1   22  11       1    _  _  547 2  c  32  21       2   b  _  _  678 3  f  56  89       3   c  _  _  34 4  h  65  90       4   d  _  _  67899 .                  5   e  _  _  875 .                  6   f  _  _  875 .                  7   g  _  _  875                    8   h  _  _  875  resulting sheet 2       b   c   d... 1   22  11  547 2  b  _   _   678 3  c  32  21  34 4  d  _   _   67899 5  e  _   _   875 6  f  56  89  875    7  g  _   _   875 8  h  65  90  875 . . . 

so, thoughts appreciated, , code welcome!

you might consider getting code add vlookup formula sheet2 b1 , c1, copying formula down (if have lots of rows, calculation time considerable, though). copy/pastespecial values in sheet2 b , c rid of formula.

alternatively can loop through values in sheet2 column , match in sheet1.

let me know if need sample code.