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

Question

In Excel, how I can merge values of Column B based on common values on Column A?

Basically what I need is some thing like this

see here: https://stackoverflow.com/questions/54582741/excel-return-range-of-values-based-on-criteria for a UDF that will return the concatenation. — Scott Craner, Jul 02 '19 at 19:04
Thanks Scot , but this s a little bit different that what you recommended as I do not have any hard coded string like "approved" — Mona Coder, Jul 02 '19 at 19:12
The formula in C2 would be: `=IF(A2<>A1,TEXTJOINIFS(B:B,", ",A:A,A2),"")` — Scott Craner, Jul 02 '19 at 19:14
C1 of your example would just be the `TEXTJOINIFS(B:B,", ",A:A,A2)` part — Scott Craner, Jul 02 '19 at 19:15
Actually use this one in C1 and copy down: `=IF(MATCH(A1,A:A,0)=ROW(A1),TEXTJOINIFS(B:B,", ",A:A,A1),"")` It will work if the data is sorted or not and only put the output in the first occurrence of the value in column A. — Scott Craner, Jul 02 '19 at 19:19
Mona: you have easily been here enough to know that unresearched questions are discouraged here. — halfer, Jul 03 '19 at 20:34
I would additionally note (again) that your questions still are not of a particularly good quality. Your English seems fine to me, so you have completely understood the several requests for technical writing that have been made to you - you have just chosen to ignore them, so that you can beg, bow, scrape and plead in your posts instead. That is not ideal for the site, as it takes volunteer editors away from fixing problems in other posts. — halfer, Jul 03 '19 at 20:56

score 9 · Accepted Answer · answered Jul 02 '19 at 19:23

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.

Thanks Scot but `=IF(MATCH(A1,A:A,0)=ROW(A1),TEXTJOINIFS(B:B,", ",A:A,A1),"")` it is not working I am getting the `#NAME?` error — Mona Coder, Jul 02 '19 at 19:26
You have to put the code above in a module. Then it will work just like any formula. — Scott Craner, Jul 02 '19 at 19:27
Did you put it in a general module or the worksheet code? If it is in a general Module it should work. The above examples are using that code unmodified. I placed it in a module and then used the formula as described. — Scott Craner, Jul 02 '19 at 19:34

Jonathan Harker · Answer 2 · 2019-07-03T05:56:47.740

1

Here is my untested code of course. The code below uses 2 loops to add the information.

dim X as integer
dim X2 as integer

dim match as string

X = 1

do while sheets("sheet1").range("A" & X).value <> ""

    sheets("sheet1").range("C" & X).value = sheets("sheet1").range("B" & X).value
    match = sheets("sheet1").range("A" & X).value        
    X2 = X + 1

        do while sheets("sheet1").range("A" & X2).value = match

            sheets("sheet1").range("C" & X).value = sheets("sheet1").range("C" & X).value + ", " + sheets("sheet1").range("B" & X2).value 
            X2 = X2 + 1

        loop

    X = X2
    X = X + 1

Loop

edited Jul 03 '19 at 05:56

answered Jul 02 '19 at 19:15

Jonathan Harker

78
1
11

1

If you format it with proper cases and indentations, then - why not? – JohnyL Jul 02 '19 at 20:13
Ok I get your point - it was a lazy answer @JohnyL - I have made the edits and added the variable declarations :) – Jonathan Harker Jul 03 '19 at 05:58
Ok, earned for efforts )) – JohnyL Jul 03 '19 at 06:01
Thanks @JohnyL you have officially welcomed me to the community my rep was 14 and this was my first answer :) – Jonathan Harker Jul 03 '19 at 06:03
Jonathan: it's OK to let new users know that we have voting/acceptance systems here, but in general we would rather people did not ask for votes. If you create good material, votes will come organically. – halfer Jul 03 '19 at 20:36

score 1 · Answer 3 · answered Jul 02 '19 at 19:23

1

Use an array formula:

=TEXTJOIN(", ",TRUE,IF(A$1:A$15=A1,B$1:B$15,""))

(Use CTRL-SHIFT-ENTER instead of ENTER to enter the formula)

answered Jul 02 '19 at 19:23

L. Scott Johnson

4,213
2
17
28

I do not think Excel Plus 2016 has TEXTJOIN – Scott Craner Jul 02 '19 at 19:26
Looks like Scott is right I cant' find `TEXTJOIN` here – Mona Coder Jul 02 '19 at 19:27
@MonaCoder I do have a UDF that mimics the regular TEXTJOIN also. It can be found here: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell – Scott Craner Jul 02 '19 at 19:29

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

3 Answers3

Linked

Related