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

So if A1 thru A23 contain
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


Then
=mergem_con(A1:A23,"=5")
will return
5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,2 3

for positives use 0
for negatives use <0
for all use something like -1.795E+307


This will also work over disjoint ranges. It ignores blanks, text, and dates.
--
Gary's Student


"romelsb" wrote:

thanks for passing by Sir Jerry,



....see a path forward.

Me too,

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



"Gary''s Student" wrote:

Thanks for the warning. I will give romelsb the function requested as a
concatenated string. It is also possible to build a sub-range thru UNION


What intrigues me, what I had never considered doing in the past, was
passing criteria as a string. I moaned and groaned about not being able to
use SUMIF and COUNTIF over dis-joint ranges. Well now I see a path forward.
--
Gary's Student


"JLatham" wrote:

Gary,
Just be careful if there are very large numbers of cells/ranges involved. I
tried doing something similar once, and had success on relatively small
numbers of results concatenated together, but there came a point where the
length of the string itself became a problem. It was too long to display
completely in a cell, and it was too long to be used as the parameter for a
Range("myConcatenatedRangeList").Select type of operation and get results.
Ended up, with help from PapaDos who we see around here all too seldom, using
Union to accomplish the task.

"Gary''s Student" wrote:

It can be done easily using something like

Function well_is_it(s As String, r As Range) As Boolean
Dim ss As String
ss = "=" & r.Value & s
well_is_it = Evaluate(ss)
End Function

So that is A1 contains a 1
then
=well_is_it("0",A1)
returns TRUE

By tomorrow I'll post a version of mergem that can process a simple criteria
as a string.

--
Gary''s Student


"romelsb" wrote:

Yap$

we can do it step by step and one at a time...
maybe one formula may be enough
4. =mergem("1","range") to collect all numbers based on search key
"<=myNumber (either + or -)"

$!!!! "<=myCELL (either + or -)"!!!!!$
thanks Sir Gary''s Student

Maybe u can now remove the word "Student" - more power
driller
--
*****
birds of the same feather flock together..



"Gary''s Student" wrote:

Your request is interesting. It is the equivalent to creating a function like

=well_is_it("0",A1) that would match the contents of A1 against the
criteria passed to the function as a string.



I'll think about it. (may be using the evaluate function???)
--
Gary''s Student


"romelsb" wrote:

Hello Gary''s Student,

from your udf below,
------
"Here is a simple function that concatentates a range of cells:"

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k < 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function
------------
I am interested with yourUDF for my design build-up - filtering large amount
of datas in a stroke of a single key.

Can i request another version wherein the concatenated result is based on
two condition.
something like this
for numbers only with search key conditions
1. =mergem("POS","range") to collect all positive numbers
result like {1,8.3,6}
2. =mergem("NEG","range") to collect all negative numbers
result like {-1,-8.3,-6}
3. =mergem("ALL","range") to collect all numbers
4. =mergem("1","range") to collect all numbers based on search key
"<=myNumber (either + or -)"

result like {-1,1,-8.3,8.3,-6,6}

is it possible to use the lookup function, something like this
=lookup(8.3,"mergem(formula1)","mergem(formula2)"

thats all for the start, if possible in excel only...

thanks
driller.

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

whoosh,
got a rapid reply...you're magnificent...keep it up...

see u next post Sir Gary''s Student...
romelsb
--
*****
birds of the same feather flock together..



"Gary''s Student" 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
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

So if A1 thru A23 contain
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


Then
=mergem_con(A1:A23,"=5")
will return
5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,2 3

for positives use 0
for negatives use <0
for all use something like -1.795E+307


This will also work over disjoint ranges. It ignores blanks, text, and dates.
--
Gary's Student


"romelsb" wrote:

thanks for passing by Sir Jerry,



....see a path forward.

Me too,

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



"Gary''s Student" wrote:

Thanks for the warning. I will give romelsb the function requested as a
concatenated string. It is also possible to build a sub-range thru UNION


What intrigues me, what I had never considered doing in the past, was
passing criteria as a string. I moaned and groaned about not being able to
use SUMIF and COUNTIF over dis-joint ranges. Well now I see a path forward.
--
Gary's Student


"JLatham" wrote:

Gary,
Just be careful if there are very large numbers of cells/ranges involved. I
tried doing something similar once, and had success on relatively small
numbers of results concatenated together, but there came a point where the
length of the string itself became a problem. It was too long to display
completely in a cell, and it was too long to be used as the parameter for a
Range("myConcatenatedRangeList").Select type of operation and get results.
Ended up, with help from PapaDos who we see around here all too seldom, using
Union to accomplish the task.

"Gary''s Student" wrote:

It can be done easily using something like

Function well_is_it(s As String, r As Range) As Boolean
Dim ss As String
ss = "=" & r.Value & s
well_is_it = Evaluate(ss)
End Function

So that is A1 contains a 1
then
=well_is_it("0",A1)
returns TRUE

By tomorrow I'll post a version of mergem that can process a simple criteria
as a string.

--
Gary''s Student


"romelsb" wrote:

Yap$

we can do it step by step and one at a time...
maybe one formula may be enough
4. =mergem("1","range") to collect all numbers based on search key
"<=myNumber (either + or -)"

$!!!! "<=myCELL (either + or -)"!!!!!$
thanks Sir Gary''s Student

Maybe u can now remove the word "Student" - more power
driller
--
*****
birds of the same feather flock together..



"Gary''s Student" wrote:

Your request is interesting. It is the equivalent to creating a function like

=well_is_it("0",A1) that would match the contents of A1 against the
criteria passed to the function as a string.



I'll think about it. (may be using the evaluate function???)
--
Gary''s Student


"romelsb" wrote:

Hello Gary''s Student,

from your udf below,
------
"Here is a simple function that concatentates a range of cells:"

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k < 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function
------------
I am interested with yourUDF for my design build-up - filtering large amount
of datas in a stroke of a single key.

Can i request another version wherein the concatenated result is based on
two condition.
something like this
for numbers only with search key conditions
1. =mergem("POS","range") to collect all positive numbers
result like {1,8.3,6}
2. =mergem("NEG","range") to collect all negative numbers
result like {-1,-8.3,-6}
3. =mergem("ALL","range") to collect all numbers
4. =mergem("1","range") to collect all numbers based on search key
"<=myNumber (either + or -)"

result like {-1,1,-8.3,8.3,-6,6}

is it possible to use the lookup function, something like this
=lookup(8.3,"mergem(formula1)","mergem(formula2)"

thats all for the start, if possible in excel only...

thanks
driller.

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

thanks,

i've made a test and it can give a concatenated characters of +1022....it
will work for my need.

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



"Gary''s Student" 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
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

So if A1 thru A23 contain
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


Then
=mergem_con(A1:A23,"=5")
will return
5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,2 3

for positives use 0
for negatives use <0
for all use something like -1.795E+307


This will also work over disjoint ranges. It ignores blanks, text, and dates.
--
Gary's Student


"romelsb" wrote:

thanks for passing by Sir Jerry,



....see a path forward.

Me too,

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



"Gary''s Student" wrote:

Thanks for the warning. I will give romelsb the function requested as a
concatenated string. It is also possible to build a sub-range thru UNION


What intrigues me, what I had never considered doing in the past, was
passing criteria as a string. I moaned and groaned about not being able to
use SUMIF and COUNTIF over dis-joint ranges. Well now I see a path forward.
--
Gary's Student


"JLatham" wrote:

Gary,
Just be careful if there are very large numbers of cells/ranges involved. I
tried doing something similar once, and had success on relatively small
numbers of results concatenated together, but there came a point where the
length of the string itself became a problem. It was too long to display
completely in a cell, and it was too long to be used as the parameter for a
Range("myConcatenatedRangeList").Select type of operation and get results.
Ended up, with help from PapaDos who we see around here all too seldom, using
Union to accomplish the task.

"Gary''s Student" wrote:

It can be done easily using something like

Function well_is_it(s As String, r As Range) As Boolean
Dim ss As String
ss = "=" & r.Value & s
well_is_it = Evaluate(ss)
End Function

So that is A1 contains a 1
then
=well_is_it("0",A1)
returns TRUE

By tomorrow I'll post a version of mergem that can process a simple criteria
as a string.

--
Gary''s Student


"romelsb" wrote:

Yap$

we can do it step by step and one at a time...
maybe one formula may be enough
4. =mergem("1","range") to collect all numbers based on search key
"<=myNumber (either + or -)"

$!!!! "<=myCELL (either + or -)"!!!!!$
thanks Sir Gary''s Student

Maybe u can now remove the word "Student" - more power
driller
--
*****
birds of the same feather flock together..



"Gary''s Student" wrote:

Your request is interesting. It is the equivalent to creating a function like

=well_is_it("0",A1) that would match the contents of A1 against the
criteria passed to the function as a string.



I'll think about it. (may be using the evaluate function???)
--
Gary''s Student


"romelsb" wrote:

Hello Gary''s Student,

from your udf below,
------
"Here is a simple function that concatentates a range of cells:"

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k < 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function
------------
I am interested with yourUDF for my design build-up - filtering large amount
of datas in a stroke of a single key.

Can i request another version wherein the concatenated result is based on
two condition.
something like this
for numbers only with search key conditions
1. =mergem("POS","range") to collect all positive numbers
result like {1,8.3,6}
2. =mergem("NEG","range") to collect all negative numbers
result like {-1,-8.3,-6}
3. =mergem("ALL","range") to collect all numbers
4. =mergem("1","range") to collect all numbers based on search key
"<=myNumber (either + or -)"

result like {-1,1,-8.3,8.3,-6,6}

is it possible to use the lookup function, something like this
=lookup(8.3,"mergem(formula1)","mergem(formula2)"

thats all for the start, if possible in excel only...

thanks
driller.

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