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

1. ## 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. ## 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?

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

Yes, I would just need to delete them.

4. ## 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??

5. ## 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. ## 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}

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

7. ## 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>

#### Posting Permissions

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