Lookup repeated values, extract info from that row and sum other values associated

This is the second part of my whole problem. First part of the problem and the a demo file i have posted here


In Colum B where i have ticket #s and some of the tickets are repeated every month . I need to find a way to sort all the repeated tickets (within one month jan/feb etc based on column A) and output into another tab on that spreadsheet. That way i have all the unique tickets from any given month (not the year).



Thanks

@ Tigeavatar

So far i have done the following:
- Created a new tab called 'Filter Data'
- Changed 'Sheet 1' to 'Raw Data' and 'Sheet 2' to 'Filter Data'
- Open the tab 'Filter data ' and tried to run the macro BUT i get a run time error 9 highlighting 'raw data'

Need your expertise thanks

tms12,

Can you post the workbook please, so I can better help you?

~tigeravatar

tms12,

The reason you were getting an error regarding the sheetname, was because the sheet Raw Data has a space at the end of it:
-Instead of "Raw Data" it was "Raw Data "

Also, many of the ParterObj numbers were numbers stored as text. I highlighted all of column B in the "Raw Data " sheet and ran the following simple macro to convert them to numbers:

Sub convertnumbers()
    Selection.NumberFormat = "General"
    Selection.Value = Selection.Value
End Sub

Lastly, because the sheetnames had spaces in them, I needed to update a section of the code to surround sheet names in single quotes in order to retrieve the data you were looking for:

                rngNextLine.Formula = "=IF(COUNTIF('" & wsSource.Name & "'!" & rngTickets.Address & ",'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & ")>1," & _
                                      "IF(COUNTIF('" & wsDest.Name & "'!$B$" & CompareLine & ":" & rngNextLine.Offset(-1, 0).Address & ",'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & ")>0," & _
                                      """" & """" & ",'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & "),'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & ")"

After that, running the macro worked fine. I have attached a modified copy of your workbook. Here is the updated macro with your sheetnames and the updated section of code:

Sub GetUniqueTickets()
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim wsSource As Worksheet:  Set wsSource = Sheets("Raw Data ")
    Dim wsDest As Worksheet:    Set wsDest = Sheets("Filter Data")
    Dim LastTicket As Long:     LastTicket = wsSource.Range("B" & Rows.Count).End(xlUp).Row
    Dim rngTickets As Range:    Set rngTickets = wsSource.Range("B2:B" & LastTicket + 1)
    Dim rngNextLine As Range:   Set rngNextLine = Nothing
    Dim CompareLine As Long
    
    Dim MonthIndex As String:   MonthIndex = vbNullString
    Dim rngMonthStart As Range: Set rngMonthStart = Nothing
    Dim rngMonthEnd As Range:   Set rngMonthEnd = Nothing
    
    Dim iCell As Range
    For Each iCell In rngTickets
        
        Dim rngMonthTickets As Range:    Set rngMonthTickets = Nothing
        
        If iCell.Offset(0, -1).Value <> MonthIndex Then
            MonthIndex = iCell.Offset(0, -1).Value
            
            If iCell.Address <> "$B$2" Then
                Set rngMonthEnd = iCell.Offset(-1, -1)
                Set rngMonthTickets = wsSource.Range(rngMonthStart.Address & ":" & rngMonthEnd.Address)
            End If
            Set rngMonthStart = iCell.Offset(0, -1)
        End If
        
        If Not rngMonthTickets Is Nothing Then
            Dim tCell As Range
            For Each tCell In rngMonthTickets
                If wsDest.Range("B" & Rows.Count).End(xlUp).Offset(0, -1).Value <> rngMonthEnd.Value Then
                    Set rngNextLine = wsDest.Range("B" & Rows.Count).End(xlUp).Offset(2, 0)
                    CompareLine = rngNextLine.Row - 1
                Else
                    Set rngNextLine = wsDest.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
                End If
                                                      
                rngNextLine.Formula = "=IF(COUNTIF('" & wsSource.Name & "'!" & rngTickets.Address & ",'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & ")>1," & _
                                      "IF(COUNTIF('" & wsDest.Name & "'!$B$" & CompareLine & ":" & rngNextLine.Offset(-1, 0).Address & ",'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & ")>0," & _
                                      """" & """" & ",'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & "),'" & wsSource.Name & "'!" & tCell.Offset(0, 1).Address & ")"
                rngNextLine.Offset(0, -1).Value = rngMonthEnd.Value
                
            Next tCell
        End If
        
    Next iCell
    
    Dim LastUnique As Long:     LastUnique = wsDest.Range("A" & Rows.Count).End(xlUp).Row
    Dim CurrentTicket As Long:  CurrentTicket = 3
    While CurrentTicket <= LastUnique
        If wsDest.Range("A" & CurrentTicket) <> vbNullString And wsDest.Range("B" & CurrentTicket) = vbNullString Then
            wsDest.Rows(CurrentTicket & ":" & CurrentTicket).Delete Shift:=xlUp
            CurrentTicket = CurrentTicket - 1
        End If
        CurrentTicket = CurrentTicket + 1
    Wend
    
    wsDest.Range("B3:B" & LastUnique).Value = wsDest.Range("B3:B" & LastUnique).Value
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Hope that helps,
~tigeravatar