Thanks alot to both of u....

"Domenic" wrote:

Assuming that you're going to enter the formula in B10, try the
following...

B10, copied across and down:

=IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$10,$A$10),INDE X(B$1:B$10,SMALL(IF($A$
1:$A$10=$A$10,ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(B$10:B10))),"")

....confirmed with CONTROL+SHIFT+ENTER. Make sure that you add the
appropriate workbook and/or sheet names.

Hope this helps!

In article ,
"nick" wrote:

i am using the following formula to populate certain fields....

=IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$ A$10,ROW($A$1:$A$10)),ROW(1:
1)),2)),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A $10,ROW($A$1:$A$10)),ROW(1:1
)),2))

i have 10 columns in workbook 1 and i am tryin to populate all the data from
10 columns of workbook 1 in to workbook 2. but the prob is if i use the above
formula, i have to modify it in all the columns of workbook 2 to get the data
from workbook 1. what makes my job easier is if i cld use the above function
or any other to populate all the 10 columns of workbook1 in 10 different
columns of workbook 2 without having to use the formula 10 different times
and modifying it each time. its like writing the function in one cell and all
the 10 cells get populated by itself in workbook 2 with the data from
workbook 1. I used the above function as there are dupluicates in the data
and vlookup dont work in that case.

Any help wld be appreciated.