I'm trying to merge the text in rows that grouped to the previous column so each item number has one row. There are currently multiple rows per item in column b and c number but not consistent. I would like to do this individually for column b and c. File attached.
In the attached the following user defined function code is already installed. You'll find it in the VBA editor. Press Alt + F11 it will open. In the left hand pane find this file name. Under that is "Modules". Double click on that. The module will open on the right with this code already pasted in. Close the VBA editor.
The user defined function is called CONCATALL. It is written by tigeravatar. This is it.
PHP Code:
'tigeravatar ExcelForum Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String 'Created by TigerAvatar at www.excelforum.com, September 2012 'Purpose is to concatenate many strings into a single string 'Can be used with arrays, range objects, and collections
Dim DataIndex As Variant 'Used to loop through arrays, range objects, and collections Dim strResult As String 'Used to build the result string
'Test if varData is an Array, Range, or Collection If IsArray(varData) _ Or TypeOf varData Is Range _ Or TypeOf varData Is Collection Then
'Found to be an, array, range object, or collection 'Loop through each item in varData For Each DataIndex In varData 'Check if the item isn't empty If Len(DataIndex) > 0 Then 'Found the item isn't empty, check if user specified bUnique as True If bUnique = True Then 'bUnique is true, check if the item has been included in the result yet If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then 'Item has not been included in the result, add item to the result strResult = strResult & "||" & DataIndex End If Else 'bUnique is not true, add item to the result strResult = strResult & "||" & DataIndex End If End If Next DataIndex
'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
Else 'Found not to be an array, range object, or collection 'Simply set the result = varData strResult = varData End If
'Output result ConcatAll = strResult
End Function
Then in D2 array enter this formula (already done).If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Bookmarks