multiple hits in random sequences

[bill gras]

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
--
bill gras

[Roland]

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.

"bill gras" wrote:

> 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
> --
> bill gras

[bill gras]

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

bill
--
bill gras


"Roland" wrote:

> 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.
>
> "bill gras" wrote:
>
> > 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
> > --
> > bill gras