Results 1 to 9 of 9

20120123, 12:30 #1
 Join Date
 Jun 2009
 Posts
 59
 Thanks
 0
 Thanked 0 Times in 0 Posts
Concatenating columns, some without data
I have what I hope is a simple problem. I have 5 columns that need to be concatenated. Normally I could do something like the following: =A1$B1&C1&D1&E1
Unfortunately, not all columns contain data. I need to include a column between each value as well (minus the last value). Any thoughts?
Ideally, if I need to alphabetize the order as well. Example output needed below:
Would output like so: ABC,DEF,JKL,MNO,XYZLast edited by bkirby; 20120123 at 13:20.

20120123, 13:30 #2
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,494
 Thanks
 28
 Thanked 171 Times in 167 Posts
Hi
What's wrong with
=A1&","&B1&","&C1&","&D1&","&E1
What do you want to see if a column is blank???
For example, do you want to see things like
ABC,JKL,MNO,ZYZ
DEF,MNO,XYZ
ABC,DEF,MNO,XYZ
MNO,XYZ
DEF,JKL,MNO
zeddy
zeddy

20120123, 13:58 #3
 Join Date
 Jun 2009
 Posts
 59
 Thanks
 0
 Thanked 0 Times in 0 Posts
I think that will work if I can remove the trailing comma if no data exists. Also, is there a function to automatically alphabetize the order. So instead of this:
DEF,ABC,JKL,,
I'd like to see
ABC,DEF,JKL

20120124, 07:10 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
To get rid of trailing you could use something like:
=LEFT(IF(A1="","",A1&",")&IF(B1="","",B1&",")&IF(C 1="","",C1&",")&IF(D1="","",D1&",")&IF(E1="","",E1 &","),LEN(IF(A1="","",A1&",")&IF(B1="","",B1&",")& IF(C1="","",C1&",")&IF(D1="","",D1&",")&IF(E1=""," ",E1&","))1)
Or if the cells do not contain any spaces, it could be simplified to:
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",",")
To alphabetize would be easiest with a macro function, I think a formula would be much too complex.
Steve

20120124, 08:45 #5
 Join Date
 Jun 2009
 Posts
 59
 Thanks
 0
 Thanked 0 Times in 0 Posts
Steve,
That worked perfectly! Thank you! I'm curious about the macro function. Do you have any examples or a post you could point me to? Any insight is much appreciated.

20120124, 10:48 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
I don't recall any examples of a function, to concatenate and search. I am at work now and don't have time to put one together now, but perhaps later this evening (if no one else posts a solution by then). I imagine the steps to be straightforward: reading the list into an array (ignoring any blanks), sorting the array, then concantenating them.Steve

20120124, 12:47 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
Try this Add it to a module and Call it like:
=sortcombine(A1:E1)
Code:Function SortCombine(rng As Range) Dim vList, vTemp Dim iFirst As Integer, iLast As Integer Dim x As Long, y As Long vList = rng iFirst = LBound(vList, 2) iLast = UBound(vList, 2) For x = iFirst To iLast  1 For y = x + 1 To iLast If vList(1, x) > vList(1, y) Then vTemp = vList(1, y) vList(1, y) = vList(1, x) vList(1, x) = vTemp End If Next y Next x vTemp = "" For x = iFirst To iLast If Not IsEmpty(vList(1, x)) Then vTemp = vTemp & "," & vList(1, x) End If Next x SortCombine = Mid(vTemp, 2) End Function
Last edited by sdckapr; 20120124 at 18:57.

The Following User Says Thank You to sdckapr For This Useful Post:
zeddy (20120125)

20120125, 11:06 #8
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,494
 Thanks
 28
 Thanked 171 Times in 167 Posts
Hi Steve
A truly elegant reply for which you should be thanked.
Thanks.
What I like about your solution is that the range can be extended easily.
And I particularly liked the =Mid(vTemp, 2) bit which deals with the first cell(s) in the range being empty.
A cracking solution!
zeddy

20120125, 17:15 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
Just a point of clarification. When looping through the list, it only adds the nonblanks: "If Not IsEmpty(vList(1, x)) Then" takes care of the blanks.
The line of code: Mid(vTemp, 2) is because when adding a new item, it adds to the previous string a comma and the new item. Thus the first item added contains an unwanted comma with it. The "mid line" takes a subset of the string after the comma is added.
Steve