Question: Is there a way to concatenate multiple columns if the a row is duplicate? I have a spreadsheet where column A has duplicate team but there area and LD (column b and c) are different value. I would like to have a formulate at column E where it will concatenate column B and C with dash and append next row values. See the attached picture highlighted row E. Any idea how to do this with excel formula or may be VBA. I tried this formula in column E =IF(A3=A4,D3&";"&D4) but it returns false for the last duplicate row.

[![enter image description here](https://i.stack.imgur.com/p32Kh.png)](https://i.stack.imgur.com/p32Kh.png)

Answer: 
This is not possible with formulas. It requires a VBA-based solution.

I wrote a custom routine for you. Please place this in a standard code module:

```
Public Sub ConcatTeamZones()
    Const SOURCE = "A1"
    Const OUTPUT = "E1"
    Dim i&, j&, s$, v, w
    v = Range(SOURCE).CurrentRegion
    ReDim w(1 To UBound(v), 0)
    For i = 2 To UBound(w)
        If v(i, 1) <> v(i - 1, 1) Then
            w(i - 1, 0) = s
            s = s & v(i, 2) & "-" & v(i, 3)
            s = ""
        Else
            s = s & ";"
        End If
        s = s & v(i, 2) & "-" & v(i, 3)
    Next
    w(i - 1, 0) = s
    Range(OUTPUT).Resize(UBound(w)) = w
End Sub

```

And then from the worksheet with your team data, press ALT-F8 to bring up the Macro Dialog. Run the ConcatTeamZones macro.

Note 1: this assumes that column A is sorted.

Note 2: You can edit the first two lines to specify which columns contains the source (team data) and which column you wish the output.
It can be done using formulas, *it’s just a matter of perspective*:

*Assuming data is sorted by Team*

This formula gives the concatenated result with the maximum of combinations on top. Enter this formula in cell `E2` and copy till last record.

```
=CONCATENATE($D2,IF(EXACT($A2,$A3),";"&$E3,""))

```

To assign the max possible combinations to each Team enter this formula in `F2` and copy till last record.

```
=INDEX($E:$E,MATCH($A2,$A:$A,0),0)

```

[![enter image description here](https://i.stack.imgur.com/icaq8.png)](https://i.stack.imgur.com/icaq8.png)