Concatenate?

br0nc0boy

New Member
Joined
Mar 12, 2009
Messages
27
Is there a faster way to combine all the P.O# with a formula automatically, no matter how many P.O# there are? Its unpredictable on how many invoices will get pay per wire.

<table x:str="" style="border-collapse: collapse; width: 179pt;" width="239" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 56pt;" width="75"> <col style="width: 75pt;" width="100"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" width="64" height="17">Log #</td> <td class="xl25" style="width: 56pt;" width="75">Amount</td> <td class="xl27" style="width: 75pt;" width="100">P.O. Numbers</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1-a</td> <td class="xl28" x:num="45678.484426309748">45,678 </td> <td class="xl26" x:num="">1234</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1-b</td> <td class="xl28" x:num="89172.527116200319">89,173 </td> <td class="xl26" x:num="">5678</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1-c</td> <td class="xl28" x:num="66525.851809145388">66,526 </td> <td class="xl26" x:num="">9123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">2</td> <td class="xl28" x:num="22112.903166205822">22,113 </td> <td class="xl26" x:num="">2345</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">3-a</td> <td class="xl28" x:num="66653.579134719723">66,654 </td> <td class="xl26" x:num="">6789</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">3-b</td> <td class="xl28" x:num="88650.086395522914">88,650 </td> <td class="xl26" x:num="">2468</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl29">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl29">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl29">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">1</td> <td class="xl29" x:num="201376.86335165545">201,377 </td> <td class="xl26">1234/5678/9123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">2</td> <td class="xl29" x:num="22112.903166205822">22,113 </td> <td class="xl26" x:num="">2345</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">3</td> <td class="xl29" x:num="155303.66553024264">155,304 </td> <td class="xl26">6789/2468</td> </tr> </tbody></table>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
You can take out the "a" "b" "c" stuff so the log entries are clean.

Next install a UDF called StringConcat which will accept a criteria for concatenating values, AND it will return values on either side of the criterion. Here's the code:
Code:
Function StringConcat(Sep As String, ParamArray Args()) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat http://www.cpearson.com/excel/StringConcatenation.aspx  '
' This function concatenates all the elements in the Args array,       '
' delimited by the Sep character, into a single string. This function  '
' can be used in an array formula.                                     '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim m As Long
Dim R As Range
Dim numDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean

'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
    StringConcat = vbNullString
    Exit Function
End If


For N = LBound(Args) To UBound(Args)
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ' Loop through the Args
    ''''''''''''''''''''''''''''''''''''''''''''''''
    If IsObject(Args(N)) = True Then
        '''''''''''''''''''''''''''''''''''''
        ' OBJECT
        ' If we have an object, ensure it
        ' it a Range. The Range object
        ' is the only type of object we'll
        ' work with. Anything else causes
        ' a #VALUE error.
        ''''''''''''''''''''''''''''''''''''
        If TypeOf Args(N) Is Excel.Range Then
            '''''''''''''''''''''''''''''''''''''''''
            ' If it is a Range, loop through the
            ' cells and create append the elements
            ' to the string S.
            '''''''''''''''''''''''''''''''''''''''''
            For Each R In Args(N).Cells
                S = S & R.Text & Sep
            Next R
        Else
            '''''''''''''''''''''''''''''''''
            ' Unsupported object type. Return
            ' a #VALUE error.
            '''''''''''''''''''''''''''''''''
            StringConcat = CVErr(xlErrValue)
            Exit Function
        End If
    
    ElseIf IsArray(Args(N)) = True Then
        
        On Error Resume Next
        '''''''''''''''''''''''''''''''''''''
        ' ARRAY
        ' If Args(N) is an array, ensure it
        ' is an allocated array.
        '''''''''''''''''''''''''''''''''''''
        IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
            (LBound(Args(N)) <= UBound(Args(N))))
        On Error GoTo 0
        If IsArrayAlloc = True Then
            ''''''''''''''''''''''''''''''''''''
            ' The array is allocated. Determine
            ' the number of dimensions of the
            ' array.
            '''''''''''''''''''''''''''''''''''''
            numDims = 1
            On Error Resume Next
            Err.Clear
            numDims = 1
            Do Until Err.Number <> 0
                LB = LBound(Args(N), numDims)
                If Err.Number = 0 Then
                    numDims = numDims + 1
                Else
                    numDims = numDims - 1
                End If
            Loop
            ''''''''''''''''''''''''''''''''''
            ' The array must have either
            ' one or two dimensions. Greater
            ' that two caues a #VALUE error.
            ''''''''''''''''''''''''''''''''''
            If numDims > 2 Then
                StringConcat = CVErr(xlErrValue)
                Exit Function
            End If
            If numDims = 1 Then
                For m = LBound(Args(N)) To UBound(Args(N))
                    If Args(N)(m) <> vbNullString Then
                        S = S & Args(N)(m) & Sep
                    End If
                Next m
                
            Else
                For m = LBound(Args(N), 1) To UBound(Args(N), 1)
                    If Args(N)(m, 1) <> vbNullString Then
                        S = S & Args(N)(m, 1) & Sep
                    End If
                Next m
                For m = LBound(Args(N), 2) To UBound(Args(N), 2)
                    If Args(N)(m, 2) <> vbNullString Then
                        S = S & Args(N)(m, 2) & Sep
                    End If
                Next m

            End If
        Else
            S = S & Args(N) & Sep
        End If
    Else
        S = S & Args(N) & Sep
    End If
Next N

'''''''''''''''''''''''''''''''''''
' Remove the trailing Sep character
'''''''''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
    S = Left(S, Len(S) - Len(Sep))
End If

StringConcat = S

End Function

Press Alt-F11 to open the VBeditor
click INSERT > MODULE and a Module window will appear
paste in all the code above
Press Alt-F11 to close the editor and save the sheet.

Here's a sample of the data being summarized the way you wanted.

Excel Workbook
ABCDEFGH
1Log #AmountP.O. NumbersLog #TotalPO Numbers
2145,678123412013771234/5678/9123
3189,17356782221132345
4166,526912331553046789/2468
5222,1132345
6366,6546789
7388,6502468
Sheet1

Notice the array formula in H2, is must be entered with CTRL-SHIFT-ENTER to activate the array, then copy it down.
 
Upvote 0
Unfortunately you can't concatenate in Array formulas. Maybe there's another way to do it, but I don't see it. The closest I can get is to use math to create silly things like this which will never work for large amounts of data:
Excel Workbook
EFG
1120137791235678123400000000
2222113234500000000000000000000
3315530424686789000000000000000000000000
Sheet1
 
Upvote 0
Wow thank you so much that is so helpful. One more question...let just say that there are those a,b,c left. How could I count them?
 
Upvote 0
The first three arguments of the UDF ConcatIf mirror those of SUMIF. ConcatIf also has a Delimiter argument.
(The spacing in the OP suggests that the cell containing 2 is formatted as text.)
Code:
Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                stringsRange.Column - compareRange.Column)
    
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
    End Function
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=27><b>A</b><td align=center width=59><b>B</b><td align=center width=117><b>C</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">1-a</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">45,678</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">1234</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">1-b</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">89,173</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">5678</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">1-c</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">66,526</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">9123</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">22,113</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2345</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">3-a</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">66,654</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">6789</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">3-b</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">88,650</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2468</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>9</b><td bgcolor="#FFFFFF" > <FONT color="#000000">1</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">201,377</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">1234/5678/9123</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>10</b><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">22,113</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2345</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>11</b><td bgcolor="#FFFFFF" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">155,304</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">6789/2468</FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>B9:B11<td align=center>B9 <td align = left >=SUMIF($A$1:$A$6,"="&A9&"*",$B$1:$B$6)</tr>
<tr><td>C9:C11<td align=center>C9 <td align = left >=ConcatIf($A$1:$A$6,"="&A9&"*",$C$1:$C$6,"/")</tr></table>
 
Last edited:
Upvote 0
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Add a command button from the control box on sheet1 and add following codes in code window

Code:
  Private Sub CommandButton1_Click()<o:p></o:p>
      Dim row1 As Integer, col1 As Integer<o:p></o:p>
      <o:p></o:p>
      row1 = 2<o:p></o:p>
      col1 = 1<o:p></o:p>
      <o:p></o:p>
      While Sheet1.Cells(row1, col1).Value <> ""<o:p></o:p>
          concatenate Sheet1.Cells(row1, col1).Value, row1<o:p></o:p>
          <o:p></o:p>
          row1 = row1 + 1<o:p></o:p>
      Wend<o:p></o:p>
  End Sub<o:p></o:p>
  <o:p> </o:p>
  Private Sub concatenate(str As String, i As Integer)<o:p></o:p>
      Dim row As Integer, col As Integer<o:p></o:p>
      row = i + 1<o:p></o:p>
      col = 1<o:p></o:p>
      <o:p></o:p>
      While Sheet1.Cells(row, col).Value <> ""<o:p></o:p>
          If Left(str, 1) = Left(Sheet1.Cells(row, col).Value, 1) Then<o:p></o:p>
              Sheet1.Cells(i, col + 2).Value = Sheet1.Cells(i, col + 2).Value & "/" & Sheet1.Cells(row, col + 2).Value<o:p></o:p>
              Sheet1.Rows(row).Delete<o:p></o:p>
          End If<o:p></o:p>
          row = row + 1<o:p></o:p>
      Wend<o:p></o:p>
      <o:p></o:p>
  End Sub
 
Upvote 0
Mike, can you explain the usefulness/implementation of the unused final parameter "NoDuplicates" and how that is affects the results when used?
 
Upvote 0
Consider the data set in A1:B5:
1 a
1 b
2 a
1 a
1 c

=ConcatIf(A1:A5, 1, B1:B5, ",") returns "a,b,a,c"
=ConcatIf(A1:A5, 1, B1:B5, ",", TRUE) returns "a,b,c", with no duplicates.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top