# Concatenate?

#### br0nc0boy

##### New Member
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### jbeaucaire

##### Well-known Member
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.

##### Well-known Member
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

#### br0nc0boy

##### New Member
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?

#### mikerickson

##### MrExcel MVP
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:

#### Chris Bode

##### Board Regular
<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``````

#### jbeaucaire

##### Well-known Member
Mike, can you explain the usefulness/implementation of the unused final parameter "NoDuplicates" and how that is affects the results when used?

#### mikerickson

##### MrExcel MVP
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.

#### jbeaucaire

##### Well-known Member
Awesome, and more awesome. Thank you very much, that's quite the tool! I've already used it to replace my other concat-trick UDFs in 3 sheets today, and passed it on to one other MrExcel poster.

There's no substitute for good code. Thanks again.

Replies
1
Views
1K
Replies
6
Views
904
Replies
4
Views
408
Replies
18
Views
4K
Replies
3
Views
506

1,195,934
Messages
6,012,386
Members
441,694
Latest member
Elvin A

### 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?

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