Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not even sure what to call this, and any searches I've done here and via google have turned up with either too many results that aren't specific enough or none at all. Anyway, what I'm trying to do seems fairly simple, but it apparently isn't.

    I have multiple columns of data that are several rows deep. In column "A", I have a customer name. In column "C" I have customer email addresses. I may have several rows with the same customer name, but each corresponding cell in column "C" will have a different email addresses.

    What I'd like to do is to take the customer name in column A and then consolidate or concatenate the email addresses that match that customer and place them all into one cell.

    Is there a formula that will do this? Or will it require VB?

    Thanks!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you post a sample worksheet so we can see your current set up? Include a the before data and what you would like the results to look like when things are finished.

    If you have 2007, please save the book to a previous version.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='765300' date='13-Mar-2009 14:16']Can you post a sample worksheet so we can see your current set up? Include a the before data and what you would like the results to look like when things are finished.

    If you have 2007, please save the book to a previous version.[/quote]
    I have attached a sample spreadsheet. There are some blank cells in column C on purpose. Some come through that way.

    Also, the physical address in column B may sometimes be different, and I need those to remain a separate line, but to also consolidate any email addresses associated with the customer name.

    And sometimes the record is exactly the same as another one that appears.

    Sure do appreciate it.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    I think you need a special Vlookup created on this site. I cannot take any credit for it but I did copy it down. It is contained in the attached workbook.
    You can get more detail by doing a Search of this Site. Think of it as a enhanced Vlookup function.

    Its a VBA function so its best to have that function as part of the Open Workbook since this makes any reference to the function most simple.

    The function will look up your first reference and then find all exact matches to that reference in a selected list. It will then return the colunm items you desire (in your case the e-mail addresses) It will put it in one cell. It also allow you to seperate the returns with any seperator of your choice inlcuding , or ;
    If you need to have it as Text just do a copy value.
    Warning - Any Typos in the Company names will cause errors in results.

    I hope this helps. It sure has helped me.

    Tom Duthie
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was finally able to get back to looking at your post...:


    [codebox]Sub combineEmail()
    Dim i As Long, lRow As Long
    Application.ScreenUpdating = False


    Range("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
    "C2"), Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
    DataOption3:=xlSortNormal

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

    Cells(2, 4).Formula = "=IF(A2=A1,D1&IF(OR(C2=C1,C2=" _
    & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) & "," & Chr(34) & "," _
    & Chr(34) & "&C2),C2)"

    Cells(2, 4).AutoFill Destination:=Range("D2" & lRow)

    Range(Cells(2, 4), Cells(lRow, 4)).Copy
    Range(Cells(2, 4), Cells(lRow, 4)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False


    Cells(2, 5).FormulaR1C1 = "=len(rc[-1])"
    Cells(2, 5).AutoFill Destination:=Range("e2:e" & lRow)

    Range("A:E").Sort Key1:=Range("a2"), Order1:=xlAscending, Key2:=Range( _
    "e2"), Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
    DataOption3:=xlSortNormal

    Cells(2, 6).Formula = "=vlookup(A2,$A$2:$d$" & lRow & ",4,0)"
    Cells(2, 6).AutoFill Destination:=Range("f2:f" & lRow)


    Range(Cells(2, 6), Cells(lRow, 6)).Copy
    Range(Cells(2, 6), Cells(lRow, 6)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    Range("A:f").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
    "B2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
    DataOption3:=xlSortNormal

    For i = lRow To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = Cells(i - 1, 2) Then
    Cells(i - 1, 1).EntireRow.Delete
    End If
    Next

    Range("c2:e" & lRow).Delete shift:=xlToLeft
    Range("a1").Select

    Application.ScreenUpdating = True
    End Sub[/codebox]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •