I am getting #value error in a formula.comparing a text string(using
wildcard) in a column range with a text string(wild card) I want extract row
numbers that matches that string in a range as array formula.While checking
in formula evaluation,to find where I did wrong,I found results are coming
correctly at all steps except final step.At last stage of formula evaluation
I find =if(false,false,true,false,# value,false,true,#value) like this.The
true values are the results what I desire.Then I used the function
if(iserror,.That also didn't work.
Any body please help me to how can I eliminate that # value error.

Post the formula!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
I am getting #value error in a formula.comparing a text string(using
wildcard) in a column range with a text string(wild card) I want extract
row
numbers that matches that string in a range as array formula.While
checking
in formula evaluation,to find where I did wrong,I found results are coming
correctly at all steps except final step.At last stage of formula
evaluation
I find =if(false,false,true,false,# value,false,true,#value) like this.The
true values are the results what I desire.Then I used the function
if(iserror,.That also didn't work.
Any body please help me to how can I eliminate that # value error.

Default col.A clo.B col C
ROW#
1 TENKA RAMAMUR 9/S/2/02
2 RELLA ESWARAMMA 9/D/1/02
3 RELLA APPA RAO 2/H
4 TENKA PRIYANKA 1/D/1/02
5 TENKA RINA RAJU 1/W/1
6 RELLA SEKHAR 2/S/2/02
7 TENKA SRINU 1/S/2/02
8 RELLA SRINIVAS 2/S/1/02
9 TENKA VALLABH 15/S/4/08
THIS IS SAMPLE DATA.MY FORMULA IS
c1={=small(if(left(b1:b9,find("^",substitute(b1:b9 ,"/","^",3)))=match(a1,a1:a9,0)&mid(b1:b9,find("^",su bstitute(b1:b9),"/","^",1)),find("^",substitute(b1:b9,"/","^",3))-find("^",substitute(b1:b9,"/","^",1))+1),row(b1:b9)),row(1:1))}
I would like to extract those row numbersfrom col B text values that have
three "/" strings that starts with a matching col A value row number.In my
above array formula I should get result 4 at c1 7 at c2.I am getting # value
error.I am getting these desired results at formula evaluation one step
before last step.In formula evaluation i am getting
small({false;false;#value;4;#value;false;7;false;f alse},1)
will you please help to correct the formula.

"Biff" wrote:

Post the formula!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
I am getting #value error in a formula.comparing a text string(using
wildcard) in a column range with a text string(wild card) I want extract
row
numbers that matches that string in a range as array formula.While
checking
in formula evaluation,to find where I did wrong,I found results are coming
correctly at all steps except final step.At last stage of formula
evaluation
I find =if(false,false,true,false,# value,false,true,#value) like this.The
true values are the results what I desire.Then I used the function
if(iserror,.That also didn't work.
Any body please help me to how can I eliminate that # value error.

Hi

The failures are arising because there are some cells in column B that do
not have 3 "/"'s.

You are making life very difficult for yourself trying to achieve the result
in a single cell formula.
Why not have a helper column where your S/2 has been extracted, (using the
Mid,Find,Substitute formula you have)and just carry out your tests on this
new column of extracted data.
You could deal with the lack of 3 /'s in cells during this extraction by
=IF(LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))<3 , "" , your_extraction_formula....)
Then you need only look for identical values in this column, where the cells
are not equal to null.

You can always hide the column if you don't want it to show, or make it a
column far off to the right of the normally viewed area of data.



Regards

Roger Govier


TUNGANA KURMA RAJU wrote:
Default col.A clo.B col C
ROW#
1 TENKA RAMAMUR 9/S/2/02
2 RELLA ESWARAMMA 9/D/1/02
3 RELLA APPA RAO 2/H
4 TENKA PRIYANKA 1/D/1/02
5 TENKA RINA RAJU 1/W/1
6 RELLA SEKHAR 2/S/2/02
7 TENKA SRINU 1/S/2/02
8 RELLA SRINIVAS 2/S/1/02
9 TENKA VALLABH 15/S/4/08
THIS IS SAMPLE DATA.MY FORMULA IS
c1={=small(if(left(b1:b9,find("^",substitute(b1:b9 ,"/","^",3)))=match(a1,a1:a9,0)&mid(b1:b9,find("^",su bstitute(b1:b9),"/","^",1)),find("^",substitute(b1:b9,"/","^",3))-find("^",substitute(b1:b9,"/","^",1))+1),row(b1:b9)),row(1:1))}
I would like to extract those row numbersfrom col B text values that have
three "/" strings that starts with a matching col A value row number.In my
above array formula I should get result 4 at c1 7 at c2.I am getting # value
error.I am getting these desired results at formula evaluation one step
before last step.In formula evaluation i am getting
small({false;false;#value;4;#value;false;7;false;f alse},1)
will you please help to correct the formula.

"Biff" wrote:


Post the formula!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...

I am getting #value error in a formula.comparing a text string(using
wildcard) in a column range with a text string(wild card) I want extract
row
numbers that matches that string in a range as array formula.While
checking
in formula evaluation,to find where I did wrong,I found results are coming
correctly at all steps except final step.At last stage of formula
evaluation
I find =if(false,false,true,false,# value,false,true,#value) like this.The
true values are the results what I desire.Then I used the function
if(iserror,.That also didn't work.
Any body please help me to how can I eliminate that # value error.