Post date in office ledger from bank statement

[kv.singh]

I have office ledger in Column A to F (where I want to paste date)
Data copied from bank statement is stored in two tables Table- 1 from column O to S and table-2 column U to AC
If data in column D and E matches with data in column K and L then paste date mentioned in column H to column F(matching on two criteria)
If data in column E matches with data in column AC paste date shown in column U to column F (matching on single criteria)
Desired out put is marked with green color in column F

I am sure to get solution from forum members.

[JohnTopley]

The results in column F do not align with your data.

[kv.singh]

sorry for inconvenience. Now data updated in attached file.

[bakerman2]

As JT already said, with data provided you can NEVER get the result in column F according to your criteria.

Sub tst()
    T1 = Range("A4", Range("A" & Rows.Count).End(xlUp)).Resize(, 6)
    T2 = Range("H6", Range("H" & Rows.Count).End(xlUp)).Resize(, 5)
    T3 = Range("U7", Range("U" & Rows.Count).End(xlUp)).Resize(, 9)
    For i = 1 To UBound(T1)
        For ii = 1 To UBound(T2)
            If Join(Array(T1(i, 4), T1(i, 5)), "|") = Join(Array(T2(ii, 4), T2(ii, 5)), "|") Then
                T1(i, 6) = T2(ii, 1)
            End If
        Next
        For j = 1 To UBound(T3)
            If T1(i, 5) = T3(j, 9) Then
                If T1(i, 6) = vbNullString Then T1(i, 6) = T3(j, 1)
            End If
        Next
    Next
    Range("A4").Resize(UBound(T1), 6) = T1
End Sub

[Sintek]

Your output is still not correct...

Sub J3v16()
Dim Data, Chk, lr As Long, i As Long
With ActiveSheet
    lr = .UsedRange.Row - 1 + .UsedRange.Rows.Count
    With .Range("A4:F" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        Data = .Value
        For i = 1 To UBound(Data)
            Chk = Evaluate("=Match(" & Data(i, 4) & " & " & Data(i, 5) & ",K1:K" & lr & "&L1:L" & lr & ",0)")
            If IsError(Chk) Then
                Chk = Evaluate("=Match(" & Data(i, 5) & ",AC1:AC" & lr & ",0)")
                If Not IsError(Chk) Then Data(i, 6) = .Parent.Cells(Chk, 21)
            Else
                Data(i, 6) = .Parent.Cells(Chk, 8).Value
            End If
        Next i
        .Value = Data
    End With
End With
End Sub

@ bakerman2...I think it's time to start making use of Option Explicit lol...

[Sintek]

...Deleted...

[bakerman2]

Steven, as long as we get the same result...

[Sintek]

@ Rudi...just wondering why you never declare variables...

[kv.singh]

Thanks @John, @ Bakerman2 @ Sintek code is giving desired results.
One more favor please . I want to create code which will move all data from A4 to F4 to Sheet "date-post"
Then when I repeat code execution, data from A4 to F4 is moved to Sheet "date-post" below earlier moved data
after leaving two blank row above. Refer attached file

[bakerman2]

No file attached.

[kv.singh]

file attached

[bakerman2]

Sub tst()
    With Sheets("Sheet3")
        T1 = .Range("A4", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 6)
        T2 = .Range("H6", .Range("H" & .Rows.Count).End(xlUp)).Resize(, 5)
        T3 = .Range("U7", .Range("U" & .Rows.Count).End(xlUp)).Resize(, 9)
    End With
    For i = 1 To UBound(T1)
        For ii = 1 To UBound(T2)
            If Join(Array(T1(i, 4), T1(i, 5)), "|") = Join(Array(T2(ii, 4), T2(ii, 5)), "|") Then
                T1(i, 6) = T2(ii, 1)
            End If
        Next
        For j = 1 To UBound(T3)
            If T1(i, 5) = T3(j, 9) Then
                If T1(i, 6) = vbNullString Then T1(i, 6) = T3(j, 1)
            End If
        Next
    Next
    With Sheets("date-post")
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A" & .Rows.Count).End(xlUp).Offset(IIf(lRow = 1, 1, 3)).Resize(UBound(T1), 6) = T1
    End With
    With Sheets("Sheet3")
        .Range("A4", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 6).ClearContents
    End With
End Sub

[kv.singh]

Thanks once again @Bakermen
I really appreciate all forum members. I always get help from this forum.

[bakerman2]

You're welcome.

You may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.