There always seem to be new avenues to explore and learn from. I know I
generally learn something almost everyday from those more experienced than
myself. My problem with similar thing was encountered once a large number
of items (over 2000 as I recall) were being pulled into a concatenated string
- at that point it all just fell apart. I'd tested using much less than that
and was unpleasantly surprised when reality jumped up and bit me in the
buttocks!

romelsb - you're welcome, I just felt that Gary''s Student had a good idea,
that I'd warn him of a potential pit that I myself had fallen into in the
past. I think you are in very good hands ... good luck with the project.

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