6

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
lardlad
  • 99
  • 1
  • 3
  • 8
  • 1
    Do you want to use macro or excle formula? please so us your work. – Hiten004 Feb 05 '13 at 22:43
  • I have done nothing but look for answers so far. I haven't tried anything yet other than built in functions. One more thing I forgot to mention is these rows could repeat for two or fifty copies with only the counties being different. So I would need to get state and customer name merged, with the counties concatenated. – lardlad Feb 05 '13 at 22:46
  • 1
    Does anyone have a formula for this? I need to do this kind of thing to create a manageable template and most people do not know how to manage macros... – rphello101 Apr 17 '14 at 18:24

3 Answers3

2

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"
mj82
  • 5,193
  • 7
  • 31
  • 39
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).
Kit
  • 341
  • 1
  • 6
0

In the hyperlink below you'll find the solution to this question. Look for the section "How to combine duplicate rows into one (keeping unique values only)"

How To Merge Rows For many Scenarios

Update: The Add-in that performs the task is not free, but there is a 15 day free trial version

Joe
  • 9
  • 2
  • 1
    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. – James Waddington Jan 30 '15 at 22:12
  • 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. – Joe Jan 30 '15 at 22:17