formula to copy names from sheetS

[mohadin]

Hi all
please help me with this:
in sheet1 & sheet2 lists of names what I welling to have them in sheet3 without repeated names.
is it possible with formula
the attached file is a sample (in sheet3 the expected result)
thanks

 

[NARAYANK991]

Hi ,

See the attached file , and the link for an explanation.

http://www.get-digital-help.com/200...m-two-columns-using-excel-2007-array-formula/

Narayan

 

[Asheesh]

Hi,

Try the below in in Sheet 3 of cell A2 with CTRL + SHIFT + ENTER and then drag it down


IFERROR(INDEX(Arr_1,MATCH(0,COUNTIF($A$1:A1,Arr_1),0)),INDEX(Arr_2,MATCH(0,COUNTIF($A$1:A1,Arr_2),0)))


Define name as below

Arr_1 = Sheet1!$A$1:$A$21

Arr_2 = Sheet2!$A$1:$A$15


See the attached for ref.

 

[mohadin]

that's great Mr. Asheesh
it is exactly what I need.
with minor thing how can we make Arr_1 & Arr_2 flexible by mean names in sheet1 & sheet2 may be less or more than this example
thanks

 

[mohadin]

Arr_1=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
Arr_2=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A))

 

[Asheesh]

What can be the maximum limit of the ranges..

 

[mohadin]

hi sir;
well' I don't know exactly; could be up to 200