Count and concantenate (XP)

[CardfaninKC]

I am working on a report that I'd like to automate and I need a little help.

In column A, I have a Name.
In column E, I have a six digit number (called a ticket).
In column F, I have a three digit number that is unique to the number in column E (called a SubTicket).


What I need is:

The Name to appear only once (still in column A)
The amount of Subtickets each Name has (placed in column D)
The actual tickets associated with each name listed all in one cell in column G


I have attached an example sheet.

Sheet1 has the raw data
Sheet2 has what I need it to look like.

Any help is greatly appreciated.

Thanks in advance!

[HansV]

You can use Data | Filter | Advanced Filter with the Copy to, Unique records only options to create a list of unique Requestors/EmailNames in column A and B.

The formula for the number of subtickets in D2 is =COUNTIF(Sheet1!$A$2:$A$99,A2)
Fill down as far as needed.

For the list of tickets, you need a custom function: <!profile=sdckapr>sdckapr<!/profile>'s VLookupUniqueAll function from <post#=460300>post 460300</post#>. Copy the function into Word, then copy it again, and paste it into a module in your workbook.
The formula in G2 is =VLookupUniqueAll(A2,Sheet1!$A$2:$F$99,4)
Fill down as far as needed.

[LegareColeman]

Does this do what you want?

<code>
Public Sub ConslidateTickets()
Dim I As Long, lLastRow As Long, lCurUser As Long
Dim strTickets As String
Application.ScreenUpdating = False
With Worksheets("Sheet1")
lLastRow = .Range("A65536").End(xlUp).Row - 1
strTickets = ""
For I = lLastRow To 1 Step -1
If .Range("E1").Offset(I, 0) <> .Range("E1").Offset(I - 1, 0) Then
strTickets = .Range("E1").Offset(I, 0).Value & ", " & strTickets
End If
If .Range("A1").Offset(I, 0).Value = .Range("A1").Offset(I - 1, 0).Value Then
.Range("A1").Offset(I, 0).EntireRow.Delete
Else
.Range("G1").Offset(I, 0).Value = Trim(Left(strTickets, Len(strTickets) - 2))
strTickets = ""
End If
Next I
End With
Application.ScreenUpdating = True
End Sub
</code>

[CardfaninKC]

This is exactly what I needed.

Thank you very much!

[CardfaninKC]

Actually, after trying it out some more, it does work very well, but there is not a count for the # in column D.

Is that possible?

[LegareColeman]

Sorry, I missed that. Try this:

<code>
Public Sub ConslidateTickets()
Dim I As Long, lLastRow As Long, lCurUser As Long, lCount As Long
Dim strTickets As String
Application.ScreenUpdating = False
With Worksheets("Sheet1")
lLastRow = .Range("A65536").End(xlUp).Row - 1
strTickets = ""
lCount = 1
For I = lLastRow To 1 Step -1
If .Range("E1").Offset(I, 0) <> .Range("E1").Offset(I - 1, 0) Then
strTickets = .Range("E1").Offset(I, 0).Value & ", " & strTickets
End If
If .Range("A1").Offset(I, 0).Value = .Range("A1").Offset(I - 1, 0).Value Then
.Range("A1").Offset(I, 0).EntireRow.Delete
lCount = lCount + 1
Else
.Range("G1").Offset(I, 0).Value = Trim(Left(strTickets, Len(strTickets) - 2))
strTickets = ""
.Range("D1").Offset(I, 0).Value = lCount
lCount = 1
End If
Next I
End With
Application.ScreenUpdating = True
End Sub
</code>

[CardfaninKC]

That did it!

All you guys and gals are awesome here!