Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking for a formula (Office 2K SR-1)

    Here's what I need to do. In column A I've got a list of names. In column B I have a list of towns that the people in column A have lived in and in column C I have the number of years they lived in that town. But, some of the names are listed more than once because the person lived in more than one town. What I need is a formula that will look at the names in column A and any time it sees that a name is listed more than once it grabs all the cells in column B for that name and merges the town names into one cell separated by commas, while also totalling the number of years in column C. I hope this request makes some sense.

    Please let me know if you need any further information.

    Thanks,
    Troy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a formula (Office 2K SR-1)

    This probably can't be done with a formula, it will take VBA code. I'd be glad to put something together if you want to use it and will answer one question. The code would put the towns and sum of years in the row with the first instance of the person. What do you want to do with the rows for the duplicate names, delete them?
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a formula (Office 2K SR-1)

    Yes, I would just need to delete them.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Looking for a formula (Office 2K SR-1)

    What happens if two people with the same name are in the list, i.e., John Smith. Shouldn't the list group data on a unique name or id instead on just name??
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a formula (Office 2K SR-1)

    That's a good point, but in this case it wouldn't happen very often and I won't have any unique ID to use.

    Troy

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a formula (Office 2K SR-1)

    Let A1:C7 house

    {"Name","Town","Years";"A","T1",5;"B","T1",4;"C"," T2",3;"A","T2",6;"D","T3",7;"E","T4",2}

    Create a unique list of names, using Advanced Filter, say in F1:F6...

    {"Name";"A";"B";"C";"D";"E"}

    In G2 array-enter, using control+shift+enter...

    =SUBSTITUTE(MCONCAT(IF($A$2:$A$7=F2,","&$B$2:$B$7, "")),",","",1)

    In H2 enter:

    =SUMIF($A$2:$A$7,F2,$C$2:$C$7)

    Select G2:H2 and copy down.

    The results area will show...

    {"Name","Towns","Total";"A","T1,T2",11;"B","T1",4; "C","T2",3;"D","T3",7;"E","T4",2}

    MCONCAT is part of the morefunc.xll add-in, downloadable from:

    http://longre.free.fr/english/index.html

    Aladin
    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a formula (Office 2K SR-1)

    See if this will do what you want:

    <pre>Public Sub CombineTowns()
    Dim I As Long, J As Long, lLastRow As Long, lYears As Long
    Dim strWk As String
    lLastRow = Worksheets("Sheet1").UsedRange.Row + Worksheets("Sheet1").UsedRange.Rows.Count - 2
    I = 0
    With Worksheets("Sheet1").Range("A1")
    Do While I < lLastRow
    strWk = ""
    lYears = 0
    For J = lLastRow To I + 1 Step -1
    If .Offset(I, 0).Value = .Offset(J, 0).Value Then
    If strWk = "" Then
    strWk = .Offset(J, 1).Value
    Else
    strWk = .Offset(J, 1).Value & ", " & strWk
    End If
    lYears = lYears + .Offset(J, 2).Value
    .Offset(J, 0).EntireRow.Delete
    lLastRow = lLastRow - 1
    End If
    Next J
    If strWk <> "" Then
    .Offset(I, 1).Value = .Offset(I, 1).Value & ", " & strWk
    .Offset(I, 2).Value = .Offset(I, 2).Value + lYears
    End If
    I = I + 1
    Loop
    End With
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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