Concatenate Variable Array of Text Cells

[atryon]

I'm trying to Concatenate an array of cells into 1 cell but there can be
anywhere from 0 to 50 entries. I don't have a problem finding the size of
the array, but I've got the array as an offset right now and I'm unsure how
to return the text from the array. Any suggestions?

 

[Héctor Miguel]

hi, !

I'm trying to Concatenate an array of cells into 1 cell but there can be anywhere from 0 to 50 entries.
I don't have a problem finding the size of the array
but I've got the array as an offset right now and I'm unsure how to return the text from the array. Any suggestions?

1) could you be more specific regardig "where" (exactly) could it be your "anywhere" ?
and (of course) how to be sure *IF* the array is 0 or 50 entries ?

2) could you expose the formulae and/or code actually used (if any) ?

3) it might be preferable an UDF (but, knowing a little more of your unexposed "details")

regards,
hector.

 

[atryon]

I've got a list of names on one sheet, with the category they belong to at
top. On the second sheet is a list of dates and each date has a category the
falls on that date. I want to return the list of names belonging in that
category each time the category shows up for a date.

Sheet name = Groups
A B C D
Name1 Name1 Name3
Name3 Name7 Name8
Name4 Name9
Name6


Second Sheet
8/24 8/25 8/26 8/27
A C D B
Formula Cell

This is the current forumla that is the the cell under A

=OFFSET(INDIRECT(ADDRESS(2,MATCH(E16,Groups!2:2,FALSE),4,TRUE,"Groups"),TRUE),1,0,COUNTA(OFFSET(INDIRECT(ADDRESS(2,MATCH(E16,Groups!2:2,FALSE),4,TRUE,"Groups"),TRUE),1,0,100,1)),1)

I'm got the array being returned but I'm unsure how to put it into one cell
as text.

 

[Héctor Miguel]

hi, !

AFAIK, there is no way to concatenate arrays in a single cell using only worksheet-functions
you will need to create a UDF that matches the group-column and resizes up to the rows needed
there is an alternate by defining named-formulae and using ("ancient" ?) xl4-macro-functions
(but i'm sure you don't want to know about this one)

if any doubt (or further information)... would you please comment ?
regards,
hector.

__ OP __

I've got a list of names on one sheet, with the category they belong to at top.
On the second sheet is a list of dates and each date has a category the falls on that date.
I want to return the list of names belonging in that category each time the category shows up for a date.
Sheet name = Groups
A B C D
Name1 Name1 Name3
Name3 Name7 Name8
Name4 Name9
Name6
Second Sheet
8/24 8/25 8/26 8/27
A C D B
Formula Cell
This is the current forumla that is the the cell under A
=OFFSET(INDIRECT(ADDRESS(2,MATCH(E16,Groups!2:2,FALSE),4,TRUE,"Groups"),TRUE),1,0,COUNTA(OFFSET(INDIRECT(ADDRESS(2,MATCH(E16,Groups!2:2,FALSE),4,TRUE,"Groups"),TRUE),1,0,100,1)),1)
I'm got the array being returned but I'm unsure how to put it into one cell as text.

__ 1st P __