thanks Sir Tom,
something like this

=mergem_con(A1:A93,"1")

"2, 3, 3,2,2,etc"

and result for the question 1
"A1,A2,A3,A4,A5,etc" which is the address of the first result

and result for the question 2 offset(0,1) text values on the
"abc,bcd,cde,def,efg,etc" which is the content of the cell offset(0,1)

if not possible maybe another udf something like
= merger(mergem_con,r,c)

offset values (0,1) may be better if not limited to a single row or column,
some kind of generic offsets(n,m) since the mergem_con function can be used
on two direction.

best regards
romelsb

then mergem_con returns:
"2, B1,3, B2,3, B3,2, B16,2, B29,3, B30,3, B39,2, B40,3, B42,3, B43,2,
B44,3, B53,3, B61,3, B69,3, B72,2, B75,2, B79,2, B84,2, B87,2, B90,2, B93"

--
*****
birds of the same feather flock together..



"Tom Ogilvy" wrote:

if I put

=trunc(rand()*8-4)
in A1 and drag fill it down to A93

and in B1 I put
="B"&row()
and drag it down to B92

then in C1 I put
=mergem_con(A1:A93,"1")

then mergem_con returns:
"2, B1,3, B2,3, B3,2, B16,2, B29,3, B30,3, B39,2, B40,3, B42,3, B43,2,
B44,3, B53,3, B61,3, B69,3, B72,2, B75,2, B79,2, B84,2, B87,2, B90,2, B93"

without the double quotes. (the results are just an example. Whatever was
in the cell in column B would be returned.)

So I am not sure what your question is.

--
Regards,
Tom Ogilvy

"romelsb" wrote in message
...
thanks, Sir Tom,

what shall be my formula syntax to grab the adjacent cells or/and the
search
result address

romelsb the driller
--
*****
birds of the same feather flock together..



"Tom Ogilvy" wrote:

Function mergem_con(r As Range, s As String) As String
Dim once As Boolean
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
v1 = rr.offset(0,1).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
mergem_con = mergem_con & "," & v & ", " & v1
Else
mergem_con = v & ", " & v1
once = True
End If
End If
End If
Next
End Function

--
Regards,
Tom Ogilvy


"romelsb" wrote in message
...
Hello,Sirs,

i got a very useful smart udf [called mergem_con] from Gary's Student
as
simple as follows: for pure numbers.
'*************
Function mergem_con(r As Range, s As String) As String
Dim once As Boolean
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
mergem_con = mergem_con & "," & v
Else
mergem_con = v
once = True
End If
End If
End If
Next
End Function
'***************

This can have a result like (without #value error)

on b1=mergem_con(A2:A100,"1")

----
result is 1.2,1.25,1.33,1.5,1.05
which are located A33,A55,A66,A67,A99

based from the above result
i also need to grab the cell contents of the adjacent column B in same
row
on cells B33,B55,B66,B67,B99.
with result like this ........[ C-33,B-55,S-66,RB-67,FG-99 ]
column B contains pure text only.

is it possible, to extend the UDF to collect the
1. cell address on Column A of the numerical result.
2. corresponding cell content on the adjacent cell(s) Student...

this may seem unusual question, but your unique help will do a lot of
magic
for the desired eco-results.

thanks and more power to forum members,
romelsb the driller
--
*****
birds of the same feather flock together..