Excel Match Function

Question

My sheet has two columns: Column A and Column B.

Column A has a list of names, and Column Bhas their corresponding values.

Some items in the list (Column A) have duplicates with different values in Column B.

What I'm trying to do is remove the duplicates in Column A and have only one of each, with all their corresponding values in one cell.

Example:

Colmn A      Column B                     Column A      Column B
Apple           7                         Apple            7, 1
Orange          2         will be         Orange           2   
Apple           1

I'm using the formula below, but it gives me a #NAME? error:

=IF(MATCH(A2,A:A,0), contenate(B:B))

Can someone please tell me what I'm doing wrong?

@user3490456 Match returns only the first found only and will throw an error if not found. If you want this in place you will need vba. If you want to create the unique list in another column using copy/paste/remove duplicates, Then you can use TEXTJOIN if you have OFFICE 365 Excel. Otherwise it too will require vba. — Scott Craner, Oct 26 '17 at 12:47
@user3490456 To get Your output, you would need a 3rd column or VBA imo. — krib, Oct 26 '17 at 12:49
As to why you are getting the NAME error `contenate(B:B)` should be `CONCATENATE(B:B)` But as I said this will not work. If MATCH does not error it will return a concatenation of the entire column of B and not just those whose column A match A2. — Scott Craner, Oct 26 '17 at 12:50

score 0 · Answer 1 · answered Oct 26 '17 at 13:14

This short macro will put the results in columns D and E:

Sub Macro1()
    Dim M As Long, N As Long, rc As Long
    Dim i As Long, j As Long, v As String
    rc = Rows.Count

    Columns("A:A").Copy Columns("D:D")
    Range("D:D").RemoveDuplicates Columns:=1, Header:=xlNo
    M = Cells(rc, 1).End(xlUp).Row
    N = Cells(rc, 4).End(xlUp).Row

    For i = 1 To N
        v = Cells(i, 4)
        For j = 1 To M
            If Cells(j, 1) = v Then
                If Cells(i, 5) = "" Then
                   Cells(i, 5) = Cells(j, 2)
                Else
                    Cells(i, 5) = Cells(i, 5) & "," & Cells(j, 2)
                End If
            End If
        Next j
        Next i

End Sub

For example:

Excel Match Function

1 Answers1