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" <(E-Mail Removed)> wrote in message
news:E5509C31-CEC5-4D58-BAA3-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:E5509C31-CEC5-4D58-BAA3-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:FAAD11BA-B2ED-423D-992D-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E5509C31-CEC5-4D58-BAA3-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
> news:FAAD11BA-B2ED-423D-992D-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:E5509C31-CEC5-4D58-BAA3-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
news:F56246ED-C614-42A0-BEF6-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:FAAD11BA-B2ED-423D-992D-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:E5509C31-CEC5-4D58-BAA3-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message
> news:F56246ED-C614-42A0-BEF6-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:FAAD11BA-B2ED-423D-992D-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:E5509C31-CEC5-4D58-BAA3-(E-Mail Removed)...
> >> >> > 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" <(E-Mail Removed)> wrote in message
news:74C01F60-63CD-4818-AB32-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:F56246ED-C614-42A0-BEF6-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:FAAD11BA-B2ED-423D-992D-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message
>> >> >> news:E5509C31-CEC5-4D58-BAA3-(E-Mail Removed)...
>> >> >> > 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..
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>