I have got a sheet that looks like this:

B C

20 77.8
20 77.7
20 77.7
20 77.7
20 77.6
40 77.4
40 77.4
40 77.4
40 77.3
60 77.3
60 77.3
60 77.3
60 77.3
60 77.4
60 77.4
60 77.4
80 77.6
80 77.6
80
80
100
100
100
100
100
100
100
120
120
120
120
120
120
140
140
140
140 78.4
140 78.5
140 78.5
140 78.5
160 78.6
160 78.6
160 78.7
160 78.7
160 78.7
160 78.8
180 78.6
180 78.6
180 78.5
180 78.5
180 78.5
180 78.4
200 78.4
200 78.3
200 78.3
200 78.3
200 78.2
200 78.2

It starts at B1 and goes on to B... What I want is to have the range o
values in column C for all of the same values in column B.
For example, the first range would return C1:C5 (because al
coresponding values in column B are 20). And then C6:C9 for value 40 i
column B etc... But it should only return values that exist. For exapl
value 80 in column B has only 2 coresponding values in column C so onl
the range of those 2 value should be returned

--
Message posted from http://www.ExcelForum.com

In C1 enter
=IF(ISERR(FIND(" ",B1,1)0),"",VALUE(MID(B1,FIND(" ",B1,1)+1,255)))
and copy down.
HTH

"dreamer " wrote in message
...
I have got a sheet that looks like this:

B C

20 77.8
20 77.7
20 77.7
20 77.7
20 77.6
40 77.4
40 77.4
40 77.4
40 77.3
60 77.3
60 77.3
60 77.3
60 77.3
60 77.4
60 77.4
60 77.4
80 77.6
80 77.6
80
80
100
100
100
100
100
100
100
120
120
120
120
120
120
140
140
140
140 78.4
140 78.5
140 78.5
140 78.5
160 78.6
160 78.6
160 78.7
160 78.7
160 78.7
160 78.8
180 78.6
180 78.6
180 78.5
180 78.5
180 78.5
180 78.4
200 78.4
200 78.3
200 78.3
200 78.3
200 78.2
200 78.2

It starts at B1 and goes on to B... What I want is to have the range of
values in column C for all of the same values in column B.
For example, the first range would return C1:C5 (because all
coresponding values in column B are 20). And then C6:C9 for value 40 in
column B etc... But it should only return values that exist. For exaple
value 80 in column B has only 2 coresponding values in column C so only
the range of those 2 value should be returned.


---
Message posted from http://www.ExcelForum.com/

It doen't work

--
Message posted from http://www.ExcelForum.com

Post the formula that "doen't work".
Can't do anything for you with just "It doen't work."

"dreamer " wrote in message
...
It doen't work.


---
Message posted from http://www.ExcelForum.com/

Hehe, sorry syntax error...
Anyway, I just did what you said to do (copy the formula to C1) but I
get a #NAME error. It's maybe because I use a non-English Excel
version.? So I tried this in VB editor

With Range("C1")
..FormulaR1C1 = "=IF(ISERR(FIND(" ",B1,1)0),"",VALUE(MID (B1,FIND("
",B1,1)+1,255)))"

End With

but this gives a copiler error (saying that the first " " is expected
to be end of the instruction) .


---
Message posted from http://www.ExcelForum.com/

With Range("C1")
Formula = "=IF(ISERR(FIND("" "",B1,1)0),"""",VALUE(MID
(B1,FIND("" "",B1,1)+1,255)))"

testing in the immediate window:

? "=IF(ISERR(FIND("" "",B1,1)0),"""",VALUE(MID(B1,FIND(""
"",B1,1)+1,255)))"
=IF(ISERR(FIND(" ",B1,1)0),"",VALUE(MID(B1,FIND(" ",B1,1)+1,255)))

also, since you using A1 notation, you would use formula rather than
formulaR1C1

Sub tester5()
With Range("C1")
.Formula = _
"=IF(ISERR(FIND("" "",B1,1)0),"""",VALUE(MID(B1,FIND("" "",B1,1)+1,255)))"
End With
End Sub

although I doubt that formula is what you want. Your discription of what
you want is not very clear. I assume columns B and C already exist and you
want to get these ranges somewhere else, but as I said, you description is
not very clear.


--
Regards,
Tom Ogilvy

"dreamer " wrote in message
...
Hehe, sorry syntax error...
Anyway, I just did what you said to do (copy the formula to C1) but I
get a #NAME error. It's maybe because I use a non-English Excel
version.? So I tried this in VB editor

With Range("C1")
FormulaR1C1 = "=IF(ISERR(FIND(" ",B1,1)0),"",VALUE(MID (B1,FIND("
",B1,1)+1,255)))"

End With

but this gives a copiler error (saying that the first " " is expected
to be end of the instruction) .


---
Message posted from http://www.ExcelForum.com/