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..

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..

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..

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..

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..

To return values, address and value from an offset location (where oset is
the offset; 1 = column B if r is columnA)
Function mergem_con(r As Range, s As String, oset as Long) As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
mergem_con = s1 & chr(10) & s2 & chr(10) s3
End Function

if you want to choose what to return of the 3 possibilities

Function mergem_con(r As Range, s As String, oset as Long, Choice as Long)
As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
Select Case Choice
Case 1
mergem_con = s1
Case 2
mergem_con = s2
Case 3
mergem_con = s3
End Select
End Function

--
Regards,
Tom Ogilvy


"romelsb" wrote in message
...
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..

Sir Tom,

after test, the incomplete result gives me the last searched value only.
myCase 1 result ",5"
myCase 2 result ",A15"
myCase 3 result ",hht"

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



"Tom Ogilvy" wrote:

To return values, address and value from an offset location (where oset is
the offset; 1 = column B if r is columnA)
Function mergem_con(r As Range, s As String, oset as Long) As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
mergem_con = s1 & chr(10) & s2 & chr(10) s3
End Function

if you want to choose what to return of the 3 possibilities

Function mergem_con(r As Range, s As String, oset as Long, Choice as Long)
As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
Select Case Choice
Case 1
mergem_con = s1
Case 2
mergem_con = s2
Case 3
mergem_con = s3
End Select
End Function

--
Regards,
Tom Ogilvy


"romelsb" wrote in message
...
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..

Incomplete modification - but don't be afraid to debug it yourself. Any,
see corrections.


Function mergem_con(r As Range, s As String, _
oset as Long) As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = s1 & "," & v
s2 = s2 & "," & va
s3 = s3 & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
mergem_con = s1 & chr(10) & s2 & chr(10) s3
End Function

-----------------

Function mergem_con(r As Range, s As String, oset as Long, Choice as Long)
As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = s1 & "," & v
s2 = s2 & "," & va
s3 = s3 & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
Select Case Choice
Case 1
mergem_con = s1
Case 2
mergem_con = s2
Case 3
mergem_con = s3
End Select
End Function

--
Regards,
Tom Ogilvy

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

after test, the incomplete result gives me the last searched value only.
myCase 1 result ",5"
myCase 2 result ",A15"
myCase 3 result ",hht"

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



"Tom Ogilvy" wrote:

To return values, address and value from an offset location (where oset
is
the offset; 1 = column B if r is columnA)
Function mergem_con(r As Range, s As String, oset as Long) As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
mergem_con = s1 & chr(10) & s2 & chr(10) s3
End Function

if you want to choose what to return of the 3 possibilities

Function mergem_con(r As Range, s As String, oset as Long, Choice as
Long)
As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
Select Case Choice
Case 1
mergem_con = s1
Case 2
mergem_con = s2
Case 3
mergem_con = s3
End Select
End Function

--
Regards,
Tom Ogilvy


"romelsb" wrote in message
...
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..