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.

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20120123, 13:30 #2
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,472
 Thanks
 22
 Thanked 167 Times in 163 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,158
 Thanks
 14
 Thanked 315 Times in 309 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,158
 Thanks
 14
 Thanked 315 Times in 309 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,158
 Thanks
 14
 Thanked 315 Times in 309 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,472
 Thanks
 22
 Thanked 167 Times in 163 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,158
 Thanks
 14
 Thanked 315 Times in 309 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