Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    concatenating multiple members (2003)

    Goo dafternoon. I have an excel sheet that has three columns. Column B & C are the ones I need to concentrate on. Column B contains cells that have multiple member starting around row 76. Column C has multiple members almost from the beginning.

    My question is this, is there a way to run a concatenation on all of the members? Please see the attached file for more clarification.

    Thank you.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: concatenating multiple members (2003)

    Welcome to Woody's Lounge!

    The worksheet that you attached already contains concatenated values in both column B and column C, so I don't understand what you want to accomplish. Could you provide a few examples, based on the sample worksheet?

  3. #3
    New Lounger
    Join Date
    Jun 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: concatenating multiple members (2003)

    Sorry, I didn't realize they looked that way. They are actually a data dump.

    Onto the question:

    In cell B1 there are values of 101110, 101123, 101180, 101120, 101122
    In cell C1 there are values of 400010, 400015 and so on.
    I need to be able to concatenate in this format: 101110.400010, 101110.400015 (finishing out cell C1), then 101123.400010, 101123.400015 (and finishing out cell C1) and so on until every member of cell B1 is concatenated with every member of C1.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: concatenating multiple members (2003)

    Hmm, that would result in VERY long entries. For example, B52 has 21 entries and C1 5 entries, so there are 21*5 = 105 combinations. Do you really want that? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  5. #5
    New Lounger
    Join Date
    Jun 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: concatenating multiple members (2003)

    Yes, that is exactly what I need. Right now I'm plugging away by hand.

    I need all of the entries so I can load them back into a database.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: concatenating multiple members (2003)

    Hmm, strange database.

    For what it's worth, you can use this macro:
    <code>
    Sub Conc()
    Dim r As Long
    Dim m As Long
    Dim s As String
    Dim b As String
    Dim pb() As String
    Dim c As String
    Dim pc() As String
    Dim i As Integer
    Dim j As Integer
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 1 To m
    s = ""
    b = Replace(Range("B" & r), " ", "")
    pb = Split(b, ",")
    c = Replace(Range("C" & r), " ", "")
    pc = Split(c, ",")
    For i = LBound(pb) To UBound(pb)
    For j = LBound(pc) To UBound(pc)
    s = s & ", " & pb(i) & "." & pc(j)
    Next j
    Next i
    Range("D" & r) = Mid(s, 3)
    Next r
    End Sub
    </code>
    My version of Excel (2002 SP3) cannot display all results correctly in the cells themselves, but the formula bar shows the correct values.

Posting Permissions

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