Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Our Goal is to merge the names (B) when the data in A is equal. The formula below looks at the next line (A), if it's the same, it merges data (name) from B. The formula is in C, so the result of the merged names will appear in C. =IF(A2<>A3,B2, B2&" , "&B3)

Example: if A2:XYZ and A3:XYZ then merge B2:Sam and B3:Dan in C = "Sam, Dan".

Problem: We need to display all the names merged into one cell (C) that have a matching A, skipping the duplicates..

If A1:A4 = XYZ, and B1:Sam B2=Dan B3=Dan B4=Joe` Then results in C should read "Sam, Dan, Joe"

Any suggestions would be appreciated.

share|improve this question
    
You're going to probably need an if loop in VBA to do this. If you have the correct formula, can you loop it as a macro? Can you post your workbook? –  Raystafarian Feb 7 '12 at 20:28
    
Not sure how to attach the workbook to superuser. Honestly, I try to avoid loops because in many cases they take longer and there are so many Excel functions that can often do the job. Would an Advanced Filter with Unique=True, somehow work for this situation? –  LtDan Feb 8 '12 at 15:41
    
You can edit your post, upload your file to a server somewhere, and link it in the post. I'll do some digging in the mean time –  Raystafarian Feb 8 '12 at 15:44
    
Why don't you just data - remove duplicates before performing your =if() function? What excel are you using? –  Raystafarian Feb 8 '12 at 16:58

1 Answer 1

This VBA will sort ascending, remove duplicates, and give you output listing

Sub test()
Application.ScreenUpdating = False
' Sort Ascending
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A:B")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Remove Duplicates
    ActiveSheet.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

'concatenate
Dim data, numrows As Long, result, i As Long, n As Long


If Range("a1") = "" Then Exit Sub

With Range("a1", Cells(Rows.Count, "a").End(xlUp)).Resize(, 2)

    .Sort key1:=Range("a1"), Header:=xlNo
    data = .Value
    numrows = UBound(data)
    ReDim result(1 To numrows, 1 To 1)


    For i = 1 To numrows

    temp = data(i, 1)
    result(i, 1) = result(i, 1) & data(i, 2)

    For n = i + 1 To numrows

        If data(n, 1) = temp Then result(i, 1) = result(i, 1) & ", " & data(n, 2) Else Exit For

    Next

    i = n - 1

    Next

    .Offset(, 2).resize(,1) = result

    End With

    Application.ScreenUpdating = True


End Sub
share|improve this answer
    
I appreciate the help. I never mentioned that we want to remove/delete the entire duplicate record. We are just tryingto list the names (from colB) where there is a match in colA. The VBA you posted above removes the entire row that has a duplicate in colA - And it puts the header from colA and ColB at the end as the next row/record and moves all the data from A&B up to row 1. Any ideas on where this went wrong? –  LtDan Feb 8 '12 at 18:49
    
@LtDan removes duplicates of ColA & ColB, we do want that if we're going to go in my way of thinking (which I admit may not be the best). And it does do something weird with the last row. hm. I was hoping someone could expand on it. –  Raystafarian Feb 8 '12 at 18:53
    
@LtDan I put the rest of the code in the answer if you want to adjust it to run properly, feel free –  Raystafarian Feb 8 '12 at 21:46
    
@LtDan the bug in the code is fixed. This will sort, remove dupes and create a listing for you. It may not be the exact solution you're looking for, but it gives you the result you're looking for. Try copying you data to a new sheet and then running the macro to keep your original data intact –  Raystafarian Feb 12 '12 at 12:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.