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.
Here's how I would do it...

Cell "A1": =COUNTIF(B$2:B2,B2)&B2 - This is to create a unique key. Copy down the length of your table

Then I would use an advanced query (with vba maybe) to create a list of unique values for team in the "F" column

Cell "G2": =VLOOKUP("1"&F2,A:D,3,0)&"-"&VLOOKUP("1"&F2,A:D,4,0)&IF(ISERROR(VLOOKUP("2"&F2,A:D,3,0)),"",", "&VLOOKUP("2"&F2,A:D,3,0)&"-"&VLOOKUP("2"&F2,A:D,4,0))&IF(ISERROR(VLOOKUP("3"&F2,A:D,3,0)),"",", "&VLOOKUP("3"&F2,A:D,3,0)&"-"&VLOOKUP("3"&F2,A:D,4,0))&IF(ISERROR(VLOOKUP("4"&F2,A:D,3,0)),"",", "&VLOOKUP("4"&F2,A:D,3,0)&"-"&VLOOKUP("4"&F2,A:D,4,0))

This function creates your combined references. It would be longer if you expected more than 4 occurrences of teams.

Just copy "IF(ISERROR(VLOOKUP("4"&F2,A:D,3,0)),"",", "&VLOOKUP("4"&F2,A:D,3,0)&"-"&VLOOKUP("4"&F2,A:D,4,0))" and change the "4" to "5" etc

You could hide column A (to tidy up).

Sorry, I tried to include an image but insufficient reputation :-)