Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count and concantenate (XP)

    This is exactly what I needed.

    Thank you very much!

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •