match two column value in excel workbook of 80k rows then write name to new sheet and corresponding-CodePudding

I have workbook with one sheet and 80k lines as shown below. same Client may come up 100 times in sheet1.i need to look for value under ddindex value "1" and value under tier value "2", if these condition match then pick client name and put in new sheet ( sheet2) with their value from column data size. If same client comes again using above condition while going row by row in sheet1 then add( sum it with previous value) data size in second sheet ( sheet2). And also get latest created date and expiry date for same client in second sheet. any idea how to achieve this using VBA ??

so far i come up below code

Option Explicit
Sub find()
    Dim i As Long
    Dim sheets As Variant
    Dim sheet As Variant
       
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
            
    Set ws = ThisWorkbook.sheets("Sheet2")
    
    For i = 2 To ActiveSheet.sheets("sheet1").Range("A2").End(xlDown).Row
             If Cells(i, 4).Value = 1 And Cells(i, 6).Value = 2 Then
                  ws.Range(1 & i).Value = Cells(i, 1).Value
                  ws.Range("A" & i).Value = Cells(i, 1).Value
              End If
    Next
             
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

CodePudding user response：

Try something like this:

Sub Summarize()
    Dim i As Long, ws1 As Worksheet, ws2 As Worksheet, data, m, client
    Dim dict As Object, dataOut, rw As Long
    
    Set dict = CreateObject("scripting.dictionary") 'for tracking unique client id's
    
    Set ws1 = ThisWorkbook.sheets("Sheet1")
    Set ws2 = ThisWorkbook.sheets("Sheet2")
    
    data = ws1.Range("A2:F" & ws1.Cells(Rows.Count, 1).End(xlUp).Row).Value 'data to array
    ReDim dataOut(1 To UBound(data, 1), 1 To UBound(data, 2))  'size output array
    
    rw = 0
    For i = 1 To UBound(data, 1)                       'loop over rows in the array
        If data(i, 5) = 1 And data(i, 6) = 2 Then      'processing this row?
            client = data(i, 1)
            
            If Not dict.exists(client) Then   'first time for this client?
                rw = rw   1                   'increment "row" counter
                dict.Add client, rw           'store position in dictionary
                dataOut(rw, 1) = client       'add the client id
            End If
            rw = dict(client)                 'find the array "row" to update
            If data(i, 2) > dataOut(rw, 2) Then
                dataOut(rw, 2) = data(i, 2)
                dataOut(rw, 3) = data(i, 3)
            End If
            dataOut(rw, 4) = dataOut(rw, 4)   data(i, 4)
            dataOut(rw, 5) = data(i, 5)
            dataOut(rw, 6) = data(i, 6)
        End If
    Next
    
    'drop the summarized data on the worksheet
    If rw > 0 Then ws2.Range("A2").Resize(rw, UBound(data, 2)).Value = dataOut
    
End Sub