Excel - Merge rows with common values and concatenate the differences in one column

Question

I would like to merge rows with common values and concatenate the differences in one column.

I think the easiest thing to do is show you an example.

Input:

Customer Name   |   NEW YORK    |   ALBANY 
Customer Name   |   NEW YORK    |   CLINTON    
Customer Name   |   NEW YORK    |   COLUMBIA
Customer Name   |   NEW YORK    |   DELAWARE
Customer Name   |   NEW YORK    |   DUTCHESS  
Customer Name   |   VERMONT     |   BENNINGTON  
Customer Name   |   VERMONT     |   CALEDONIA
Customer Name   |   VERMONT     |   CHITTENDEN
Customer Name   |   VERMONT     |   ESSEX
Customer Name   |   VERMONT     |   FRANKLIN

Desired output:

Customer Name   |   VERMONT     |   BENNINGTON,CALEDONIA,CHITTENDEN,ESSEX,FRANKLIN
Customer Name   |   NEW YORK    |   ALBANY,CLINTON,COLUMBIA,DELAWARE,DUTCHESS

I did see some other posts on this but I don't think they were exactly what I was trying to do.

Solution 1

If by | you mean separete cell, then following macro (Excel 2007) should do the trick (your data begins in cell A1):

Application.ScreenUpdating = False

last_row = Cells(Rows.Count, 1).End(xlUp).Row

'first: make sure data is sorted
Sort.SortFields.Clear
Sort.SortFields.Add Key:=Columns("A:A"), SortOn:=xlSortOnValues
Sort.SortFields.Add Key:=Columns("B:B"), SortOn:=xlSortOnValues
Sort.SortFields.Add Key:=Columns("C:C"), SortOn:=xlSortOnValues

With Sort
    .SetRange Range("A1:C" & last_row)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

'then: join text until key values in two neighboring row changes
myText = ""
myPos = 1

For i = 1 To last_row
    If Cells(i, 1).Value <> Cells(i + 1, 1).Value Or Cells(i, 2).Value <> Cells(i + 1, 2).Value Then
        Cells(myPos, 5).Value = Cells(i, 1).Value
        Cells(myPos, 6).Value = Cells(i, 2).Value

        myText = myText & Cells(i, 3).Value
        Cells(myPos, 7).Value = myText
        myText = ""
        myPos = myPos + 1
    Else
        myText = myText & Cells(i, 3).Value & ","
    End If
Next i

Application.ScreenUpdating = True
MsgBox "Done"

Solution 2

In a new sheet, you can use these formulae to create a list of unique customer names and states.

You need to use two coordinating functions, in a new sheet enter the following function in A2

=IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIFS(Sheet2!$A$1:A1, Sheet1!$A$2:$A$6,Sheet2!$B$1:B1, Sheet1!$B$2:$B$6), 0)),"")

and the following function in B2

=IFERROR(INDEX(Sheet1!$B$2:$B$6, MATCH(0, COUNTIFS(Sheet2!$A$1:A1, Sheet1!$A$2:$A$6,Sheet2!$B$1:B1, Sheet1!$B$2:$B$6), 0)),"")

For this to work, the following needs to be true

1. This needs to be placed in a second worksheet (rename Sheet2 accordingly)
2. Sheet1!$A$2:$A$6 and Sheet1!$B$2:$B$6 should be modified (sheet name and range) to contain your full list of customer names and states respectively. If the list is too long, the last entry in your list of unique values will b 0
3. The above formulae should be pasted in Sheet2!A2 and Sheet2!B2 respectively
4. These are array formulae. It should be entered into the designated cell and then submitted by hitting ctrl+alt+enter
5. After entering the initial formulae into A2 and B2 as an array, copy and paste the formulae down Column A and Column B until unique names no longer appear.

(This answer is adopted from this question. There are a few variants as other answers)

Use an array function to return counties in ColumnC. Paste the formula in C2 as an array (with updated references), and then copy it down the list.

=TEXTJOIN(", ",TRUE,IF((Sheet2!A2=Sheet1!$A$2:$A$6)*(Sheet2!B2=Sheet1!$B$2:$B$6),Sheet1!$C$2:$C$6,""))

Brief explination on how these functions work:

-Function 1:

1. the COUNTIFS looks at your source data, and returns an array with 1 if it is already in the list, and a 0 if it is not.
2. MATCH looks at the COUNTIFS array, and identifies the first 0
3. INDEX looks at your source data, and returns the row identified by MATCH

-Function2:

1. The IF function creates two arrays of TRUE or FALSE based on whether the source data matches the unique customer and state of the row.
2. Those arrays are multiplied, creating a new array that is TRUE only if both entries were TRUE. (meaning both the customer and state matched)
3. The IF statement returns the county if true, and if false, and empty cell ""
4. TEXTJOIN filters out the empty cells (That is what the TRUE is doing) and joins them together with a comma and a space (the delimiter).

Comments

[James Waddington]

It's better to put the relevant information in your answer as well as in the link. This way your answer will stay valid even if the link goes down, and people will be able to see it in context.

[Admin]

My bad James. This answer is kind of the easy way out. I'm not sure how the add in performs the necessary functions that will create the final merged cells.