multiple hits in random sequences

[Guest]

Hi

I have in column "B" formulas with the results of " 1 Cotton " , " 2 Blue " ,
"3 British " and some rows have #VALUE! The number of rows in Column "B"
are in mutiple random sequences . " 1 Cotton " , " 2 Blue " , "3 British "
are allways on the first row of a sequence. I can not seperate the number
from the word
(1 Cotton) it is the result of a function . In column "AW" I need multiple
hits of column
"B" eg:-

B AW
1 Cotton 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
#VALUE! 1 Cotton
2 Blue 2 Blue
#VALUE! 2 Blue
#VALUE! 2 Blue
#VALUE! 2 Blue
#VALUE! 2 Blue
3 British 3 British
#VALUE! 3 British
#VALUE! 3 British
#VALUE! 3 British
#VALUE! 3 British
#VALUE! 3 British
#VALUE! 3 British
down to 3000 rows

Can any one help please ?

regards bill

 

[Guest]

Assuming you have headings in row 1 and data below that,

in cell AW2 try the formula =if(iserror(b2),aw1,b2).

Then copy the formula down column AW.

 

[Guest]

Hi Roland
Thank you for your reply it is just wat I was looking for , it's perfect!
Thanks

bill