2nd cry for help-3 variable length lists of data that need to be concatenated in to one column

[tur13o]

I have 3 variable length lists of data that need to be concatenated in to
one column.



Presently the following formula works (This is typed in to A1 and copied
down the column using the drag handle so in cell A2 refs to C1,D1,E1 are
automatically changed to C2,D2,E2 and so on):



=IF(ROW()<=COUNTA(C:C),C1,IF(ROW()<=(COUNTA(C:C)+COUNTA(D)),OFFSET(D1,(COU
NTA(C:C)),0,1,1),IF(ROW()<=(COUNTA(C:C)+COUNTA(D)+COUNTA(E:E)),OFFSET(E1,-
(COUNTA(C:C)+COUNTA(D)),0,1,1),"")))



Result - a list of data in column A which is the list of data in column C
plus data in column D plus data in column E.



But I now want to make the lists of data in Cols C,D,E dynamic named ranges.
Creating the dynamic named ranges



andy:



=OFFSET('Input'!$C$1,0,0,COUNTA('Input'!$C:$C),1)



fred:



=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$D:$D),1)



bert:



=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$E:$E),1)





What I'd now like to do is use the named ranges in the 1st formula instead
of "C:C", "D", "E:E". And this is where I need some more brain power!
Because I can't figure it out.



The best I have discovered so far is: INDIRECT(CELL("address",andy))
Making my long formula :-



=IF(ROW()<=COUNTA(andy),INDIRECT(CELL("address",andy))....



- and it works in cell A1. But the drag formula from A1 to A2 can't
automatically increase the cell (as in C1 becomes C2 and so on) - so A2,A3.
all end up with the same data displayed in them.



Can anyone help ? If you havn't got time to figure it out - clues are just
as welcome

 

[Maurice]

First part already already appear to know about

=CONCATENATE(B2,C2,D2) or with spaces between
=CONCATENATE(B3," ",C3," ",D3 )

produces eithe ABC or A B C

 

[Maurice]

I have 3 variable length lists of data that need to b
concatenated in to
one column.

Presently the following formula works (This is typed in to A1 an
copied
down the column using the drag handle so in cell A2 refs to C1,D1,E
are
automatically changed to C2,D2,E2 and so on):


But I now want to make the lists of data in Cols C,D,E dynamic name
ranges.
Creating the dynamic named ranges

andy:

=OFFSET('Input'!$C$1,0,0,COUNTA('Input'!$C:$C),1)

fred:



=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$D:$D),1)

bert:

=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$E:$E),1)

What I'd now like to do is use the named ranges in the 1st formul
instead
of "C:C", "D", "E:E". And this is where I need some more brai
power!
Because I can't figure it out.

- and it works in cell A1. But the drag formula from A1 to A2 can't
automatically increase the cell (as in C1 becomes C2 and so on) - s
A2,A3.
all end up with the same data displayed in them.

the second part
=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$E:$E),1)

may be as a result of using absolute rather than relative values

(hope i'm not teaching you to suck eggs here)

$ symbol maintains the link to the original cell


change to =OFFSET('Input'!D1,0,0,COUNTA('Input'!E:E),1)

That should drag in any direction

to maintain the D column use

=OFFSET('Input'!$D1,0,0,COUNTA('Input'!E:E),1

 

[Maurice]

3rdly

andy:
=OFFSET('Input'!$C$1,0,0,COUNTA('Input'!$C:$C),1)

fred:
=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$D:$D),1)

bert:
=OFFSET('Input'!$D$1,0,0,COUNTA('Input'!$E:$E),1)

give each column the desired heading

and from the tool bar Insert > Name > define this gives you a dialo
box with which you can define and adjust the ranges to suit

The only thing here is i don't know how to solve your entire problem

 

[tur13o]

Thanks - but this is not what I mean.

I don't want the components of the list concatenated together - I want the
lists concatenated together. e.g. C1:C5=a,b,c,"","" ; D15=z,y,"","",""
; E1:E5=123,456,789,321,"" Then my list would be -
a,b,c,z,y,123,456,789,321. (Not - az123,by456,c789,321) The problem is the
variable length lists - I don't
want -a,b,c,"","",z,y,"","","",123,456,789,321,"". (Just for added clarity -
each item between the commas would appear in separate cells in a column -
not as a list of text all in one cell)


Sorry for not being clear on that before.

From my post before - I'm happy with the formula and the variable length
lists I have already - but want to use variable length named ranges in the
formula. (The variable length named ranges are working fine as I use them
as data validation entry criteria - and the drop down box only shows the
data in the list without loads of blank entries after)

 

[Ron Rosenfeld]

I don't want the components of the list concatenated together - I want the
lists concatenated together. e.g. C1:C5=a,b,c,"","" ; D15=z,y,"","",""
; E1:E5=123,456,789,321,"" Then my list would be -
a,b,c,z,y,123,456,789,321. (Not - az123,by456,c789,321) The problem is the
variable length lists - I don't
want -a,b,c,"","",z,y,"","","",123,456,789,321,"". (Just for added clarity -
each item between the commas would appear in separate cells in a column -
not as a list of text all in one cell)


Sorry for not being clear on that before.

If you are only going to have three or four named ranges, each one column long,
then this UDF will do what you want (I think).

If you need more ranges, and if you may have blanks within the range, then I
would rewrite the segments as a subroutine.

==============================
Function ConcatList(rng1 As Range, Optional rng2 As Range, Optional rng3 As
Range, Optional rng4 As Range) As String
Dim i As Long, j As Long
Dim rng1Count As Long, rng2Count As Long, rng3Count As Long, rng4Count As Long
Dim list()

rng1Count = rng1.Count
ReDim list(rng1Count - 1)
For i = 0 To UBound(list)
list(i) = rng1(i + 1).Text
Next i
If Not rng2 Is Nothing Then
rng2Count = rng2.Count
ReDim Preserve list(UBound(list) + rng2Count)
j = 1
For i = rng1Count To UBound(list)
list(i) = rng2(j).Text
j = j + 1
Next i
End If
If Not rng3 Is Nothing Then
rng3Count = rng3.Count
ReDim Preserve list(UBound(list) + rng3.Count)
j = 1
For i = rng1Count + rng2Count To UBound(list)
list(i) = rng3(j).Text
j = j + 1
Next i
End If
If Not rng4 Is Nothing Then
rng4Count = rng4.Count
ReDim Preserve list(UBound(list) + rng4.Count)
j = 1
For i = rng1Count + rng2Count + rng3Count To UBound(list)
list(i) = rng4(j).Text
j = j + 1
Next i
End If

For i = 0 To UBound(list)
ConcatList = ConcatList & list(i) & ","
Next i

ConcatList = Left(ConcatList, Len(ConcatList) - 1)

End Function
===============================
--ron