# Thread: Count and concantenate (XP)

1. ## Count and concantenate (XP)

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.

2. ## Re: Count and concantenate (XP)

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.

3. ## Re: Count and concantenate (XP)

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>

4. ## Re: Count and concantenate (XP)

This is exactly what I needed.

Thank you very much!

5. ## Re: Count and concantenate (XP)

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?

6. ## Re: Count and concantenate (XP)

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>

7. ## Re: Count and concantenate (XP)

That did it!

All you guys and gals are awesome here!

#### Posting Permissions

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