Based on unique id previous line delete and date should move to current status

[jawaharprm]

Hi All,
i have u lines Tech Review and Manager review but the unique id are same in column A, if a tech review has moved to manager review then tech review date should paste in a column named received date near to manager review line and tech review line should be deleted, i have enclosed a sample file with two sheet input file and output which i required.

thanks
jawahar

 

[jawaharprm]

Hi,
i worked on this code and working fine but its possible make to changes i required, this code runs on proper order if date mixed like attached file dates are updating wrongly, i have enclosed a file for your reference.

Option Explicit

Sub Button1_Click()
'Move Date as the status given

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("ITS Dotnet") '
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    
    Dim i As Long, j As Long
    Dim workFlowID As String
    Dim techReviewDate As Variant
    
    
    For i = 2 To lastRow
        
        If ws.Cells(i, 12).Value = "Sourcing Manager Review" Then
            workFlowID = ws.Cells(i, 4).Value
            
            
        For j = i - 1 To 2 Step -1
          If ws.Cells(j, 4).Value = workFlowID And ws.Cells(j, 12).Value = "Technical Review" Then
                    
         ws.Cells(i, 14).Value = ws.Cells(j, 13).Value
                    
                
          ws.Rows(j).Delete
            i = i - 1
            lastRow = lastRow - 1
           Exit For
         End If
         Next j
         End If
         Next i
    
    MsgBox "Technical Review dates moved and rows deleted!"
End Sub

 

[Marc L]

Hi, according to your post #2 attachment a starter VBA demonstration clearing the matching rows​

(the Output worksheet module is the best place to paste it) :​

Sub Demo1()
    Dim M, C&, R
   With [A1].CurrentRegion.Rows
        M = Evaluate(Replace("IF(N1:N#>"""",MATCH(D1:D#&""¤""&N1:N#,D1:D#&""¤""&M1:M#,0),"""")", "#", .Count))
        C = Application.Count(M):  If C = 0 Then Beep: Exit Sub
        Application.ScreenUpdating = False
    For Each R In M
        If IsNumeric(R) Then .Cells(R, 1).ClearContents
    Next
       .Sort .Cells(1), 1, Header:=1
       .Item(.Count + 1 - C & ":" & .Count).Clear
   End With
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[jawaharprm]

Hi Sir,
Thanks for your time, i pasted in sheet module its not working, and how to get this symbol "¤" and for what action it used, Pls tell Sir i like to know..

Thanks..

 

[Marc L]

As it well works on my side with your post #2 attachment … Symbol used as separator.​

​

 

[jawaharprm]

Ok, Thanks you Sir..