Help me consolidate excel text data

[bs0000007]

How to merge text data if there is same reference value? Prefer the output to be in new worksheet and linked to source (so if the source is changed, the result is automatically change as well)

Here the example of source, you can see Ord Number for row 3 and 4 are same
tanya1.png

And this is the output I expect
tanya2.png


Thanks.

[AliGW]

Of course it CAN be done, but WHY would you want to? merging data into one cell will cause knock-on problems when analysing your data later on.

[bs0000007]

I use it to create shipping label

[Glenn Kennedy]

One way:

Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
   
    Dim DataIndex As Variant
    Dim strResult As String
    
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
        
        For Each DataIndex In varData
            If Len(DataIndex) > 0 Then
                 If bUnique = True Then
                      If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                       trResult = strResult & "||" & DataIndex
                    End If
                Else
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
        
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
        
    Else
        strResult = varData
    End If
    
    ConcatAll = strResult
    
End Function

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


Then... In I11, copied across and down:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$10)/(($A$2:$A$10<>"")*(COUNTIF($I$10:$I10,$A$2:$A$10)=0)),1)),"")

and in J11, an array formula, copied down:
=IF(I11="","",concatall(IF($A$2:$A$10=I11,$C$2:$C$10&" ("&$D$2:$D$10&")",""),CHAR(10)))

Don't forget to wrap text in column J, or manually set row width to be deep enough.

Array formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...