Results 1 to 9 of 9
  1. #1
    Star Lounger
    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,XYZ
    Last edited by bkirby; 2012-01-23 at 14:20.

  2. #2
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 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

  3. #3
    Star Lounger
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Star Lounger
    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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
    Steve
    Last edited by sdckapr; 2012-01-24 at 19:57.

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    zeddy (2012-01-25)

  9. #8
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 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

  10. #9
    WS Lounge VIP sdckapr's Avatar
    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 non-blanks: "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •