String concatenation is a bit tricky and is not a builtin function to excel, you'll have to add this capability in. I couldn't figure out how to remove the original entry easily, but I was able to use a string concatenation User Defined Function to list all the versions of a single code in one cell.
Based on your example, all the original codes come BEFORE the hyphen, so I used the hyphen as the break-point to determining which items are "matches" on any given row.
First, you have to install the StringConcat() function into your spreadsheet: 1. Open up your workbook.
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given below)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet.
Here's the code to add:
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, R As Range, IsArrayAlloc As Boolean
Dim N As Long, m As Long, numDims As Long, LB As Long
' 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 is 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 # 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
Now, there are a LOT of string concatenation UDFs out there, many simpler than this. The benefit of THIS one is that it takes complicated array formulas as a filtering criteria. That's good. On your sheet, in cell B2, this would be the first array formula:
=stringconcat(",",IF(ISNUMBER(SEARCH(LEFT(A2,FIND("-",A2)-1),$A$2:$A$50)),$A$2:$A$50,""))
...confirmed by pressing CTRL-SHIFT-ENTER.
Curly braces { } will appear around your formula and the first set of comma-delimited string values will appear...all the ones with 131 as the "code".
The first parameter in red is the "delimiter"..you can make that anything you want. I used a comma.
The second parameter in blue is the array-filtering criteria, this time in a complicated IF() array.
Take a look, sorry I couldn't figure out how to NOT list the entry in the CODE column, too, but this at least is usable.
Bookmarks