Lookup Data stored over multiple rows and combine into one cell - Macro ?

[alex0182828]

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

I am trying to take some data stored over many rows and combine it into one cell. I have given an example below that's far better than any description i can give. Any help would be great.I don't think this can be done with a formula but i would love to be proven wrong ! Thanks. All Source Data RAN-2MM-APPLE SAPPLE-TWST-2.5MM RAN-2MM-APPLE SAPPLE-SQU-DC-2MM RAN-2MM-APPLE SAPPLE-3MM RAN-2MM-APPLE SAPPLE-2MM SAPPLE-3MM WILD SAPPLE-3MM RAN-2MM-APPLE Output Required RAN-2MM-APPLE SAPPLE-TWST-2.5MM,SAPPLE-SQU-DC-2MM,SAPPLE-3MM,SAPPLE-2MM SAPPLE-3MM WILD,RAN-2MM-APPLE <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col style="width:65pt" width="65"> </colgroup><tbody>
</tbody>

 

[skorpionkz]

try this UDF:

Function rngCONC(CellsRange As Range, UniqueTest As Boolean, Delimiter As Variant) As String

Dim wsTemp As Worksheet
Dim RangeCollection As Collection
Dim TargetArray As Variant, temp As Variant, finalOutput As Variant
Dim myRange As Range
Dim ItemsInColl As Integer

If IsMissing(Delimiter) Then Delimiter = ""

If UniqueTest = False Then
    For Each temp In CellsRange
    If Not temp = Empty Then
        If finalOutput = Empty Then
            finalOutput = temp
        Else
            finalOutput = finalOutput & Delimiter & temp
        End If
    End If
    Next temp
Else
    On Error Resume Next
    Set RangeCollection = New Collection
    For Each temp In CellsRange
        If Not temp = Empty Then
            RangeCollection.Add CStr(temp.Value), CStr(temp.Value)
        End If
    Next temp
    On Error GoTo 0
    
    For Each temp In RangeCollection
        If finalOutput = Empty Then
            finalOutput = temp
        Else
            finalOutput = finalOutput & Delimiter & temp
        End If
    Next temp
End If

rngCONC = finalOutput

End Function

Also you can ad arguments description to this UDF with below code (you need to run it only once)

Sub DescribeFunction_rngCONC()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 3) As String

   FuncName = "rngCONC"
   FuncDesc = "Concatenate text from selected range"
   Category = 7 'Text category
   ArgDesc(1) = "is a range that need to be concatenated"
   ArgDesc(2) = "is a logical value (False or True): ""False"" to include each text from selected range, ""True"" for UNIQUE values only"
   ArgDesc(3) = "(optional) to be used between elements"
    
   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub

 

[Comfy]

There is no built-in concatenate if function.

You can either use the MOREFUNC library or use a user defined function (there a quite a few examples in the web)

 

[Ron Coderre]

Excel does a fairly awful job of concatenating the way you want.
Here's a formula approach

With your sample data in A2:B7
• Put this formula in C2 and copy it down
=--(A2<>A3)

• Put this formula in D2 and copy it down
=IF(A2=A1,D1&", "&B2,B2)

• Filter the data on Col_C =1
Now you copy the visible cells and paste the values wherever you need them.

Is that something you can work with?

 

[alex0182828]

Thanks for the help all

skorpionkz I popped the UDF in my sheet but i can't seem to get it to output the data as i am not sure how it works or if i need to combine it with something else i tried a bunch of different setups but i failed I think possibly i am not smart enough !

Ron Coderre That worked a treat !

Thanks. A

 

[skorpionkz]

yeah UDF i just merge range of data (doesn't to lookup) I can change it so it would do lookup as well, but Ron gave you easiest solution