HI


thanks for your reply i have sample sheet. different names last name and first name
some of them have - or . in middle
and first name
but all of them last name and first name are samein sheet 1 and sheet2.
but i need to have the same columns in each sheet if it has the same last name and first name.

Attached Files

Book2.xls (13.5 KB, 14 views)

it's not an easy task, because there are too many variables in your names...
to be specific, first name in column A, sheet 1, PETER, RAJ, in sheet 2 is PETER, RAJ(DEV)
second entry sheet1: SUBBU PERIAY, RAM, in sheet 2: SUBBU-PERIAY, RAM
third entry: SEKAR, RAM in sheet 2: SEKAR, RAM(there are 2 spaces between names in sheet 2! )
If you say there could be an "." between names, all of this will mess up any code
only formulas that searches text within cell (not range...) will have some results (like : SEARCH("Peter";A1;1) or =IFERROR(IF(AND(SEARCH("Peter";A1;1);SEARCH("Raj"; A1;1))>0;1;0);0)
for this name, for example, PETER, RAJ(DEV) needs a formula to remove "(DEV)" from right name
My advice is , if possible, to assign an unique ID for people in your list...
searching after an ID is A LOT easier

thanks
but where can i use this code . still confusing. please help me
thanks