Merge values of column B based on common values on column A

by

You can use this UDF:

Function TEXTJOINIFS(rng As Range, delim As String, ParamArray arr() As Variant)
    Dim rngarr As Variant
    rngarr = Intersect(rng, rng.Parent.UsedRange).Value

    Dim condArr() As Boolean
    ReDim condArr(1 To Intersect(rng, rng.Parent.UsedRange).Rows.Count) As Boolean


    Dim i As Long
    For i = LBound(arr) To UBound(arr) Step 2
        Dim colArr() As Variant
        colArr = Intersect(arr(i), arr(i).Parent.UsedRange).Value
        Dim j As Long
        For j = LBound(colArr, 1) To UBound(colArr, 1)

            If Not condArr(j) Then
                Dim charind As Long
                charind = Application.Max(InStr(arr(i + 1), ">"), InStr(arr(i + 1), "<"), InStr(arr(i + 1), "="))
                Dim opprnd As String
                If charind = 0 Then
                    opprnd = "="
                Else
                    opprnd = Left(arr(i + 1), charind)
                End If
                Dim t As String
                t = """" & colArr(j, 1) & """" & opprnd & """" & Mid(arr(i + 1), charind + 1) & """"
                If Not Application.Evaluate(t) Then condArr(j) = True
            End If
        Next j
    Next i

    For i = LBound(rngarr, 1) To UBound(rngarr, 1)
        If Not condArr(i) Then
            TEXTJOINIFS = TEXTJOINIFS & rngarr(i, 1) & delim
        End If
    Next i

    TEXTJOINIFS = Left(TEXTJOINIFS, Len(TEXTJOINIFS) - Len(delim))

End Function

You would call it like this:

=IF(MATCH(A1,A:A,0)=ROW(A1),TEXTJOINIFS(B:B,", ",A:A,A1),"")

Now it does not matter if the data is sorted or not it will only put the output in column C where the value in Column A first appears.

enter image description here

enter image description here

More Related Contents:

  1. MS Excel: insert a row automatically if the content in a cell is <> that in cell of the last row?
  2. vba excel: how to copy characters from a cell to another
  3. Microsoft Excel ActiveX Controls Disabled?
  4. What is the default scope of worksheets and cells and range?
  5. Convert an entire range to uppercase without looping through all the cells
  6. Are there disadvantages in putting code into Userforms instead of modules?
  7. Properly Handling Errors in VBA (Excel)
  8. Password hash function for Excel VBA
  9. Is there a way to slow down a Web Scraper so it will pick up the code?
  10. Create a folder and sub folder in Excel VBA
  11. Aggregate, Collate and Transpose rows into columns
  12. VBA Excel sort range by specific column
  13. Select method of Range class failed via VBA
  14. Renaming Files with Excel VBA
  15. Is there a way to import data from .csv to active excel sheet?
  16. Comparison of Dictionary, Collections and Arrays
  17. Break out of a While…Wend loop
  18. How to add events to dynamically created controls (buttons, listboxes) in Excel VBA userform
  19. How to make safe API Timers in VBA?
  20. Error 1004 when setting Range.Locked
  21. How to extract groups of numbers from a string in vba
  22. Excel VBA – select multiple columns not in sequential order
  23. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  24. Application.WorksheetFunction.Match method
  25. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  26. Delete all data rows from an Excel table (apart from the first)
  27. Excel Hide/Show all tabs on Ribbon except custom tab
  28. Macros Not Showing Up in Macro Table
  29. Excel telling me my blank cells aren’t blank
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment