Good night, I hope that they are well. They excuse my English is not good.
As it indicates the title of the message council desire two sheets of Excel but have discontinuous ranks and values among them. The sheets are Accounting (columns D and E) and Bank (columns and D), I need to cross these two columns and if they are exactly equal in column G places the Conciliated word to me, on the contrary Not conciliated. Attached simple example with the awaited results. There is a note sheet with some additional additional observations.
Thank you very much by the attention and given collaboration.
Conciliation by reference and Amount
-
-
-
Re: Conciliation by reference and Amount
Hi miulrg,
The columns in the attached workbook change or we assume that will remain the same?
-
Re: Conciliation by reference and Amount
Hi michaelnicolas,
Thanks to respond, the columns will exactly remain equal for the example.
Very been thankful by the interest in my proposal
-
Re: Conciliation by reference and Amount
VBA
Code
Display MoreSub test() Dim a, i As Long, ii As Long With Sheets("accounting").Cells(1).CurrentRegion.Resize(, 7) .Columns("g").Offset(2).Resize(.Rows.Count - 2, 1).Value = "Not conciliated" a = .Value End With With CreateObject("Scripting.Dictionary") .CompareMode = 1 For i = 3 To UBound(a, 1) .Item(a(i, 2)) = VBA.Array(a(i, 4), a(i, 5), i) Next With Sheets("bank").Cells(1).CurrentRegion.Resize(, 7) .Columns("g").Offset(2).Resize(.Rows.Count - 2, 1).Value = "Not conciliated" a = .Value End With For i = 3 To UBound(a, 1) If .exists(a(i, 2)) Then For ii = 4 To 5 If a(i, ii) <> "" Then If a(i, ii) = .Item(a(i, 2))(IIf(ii = 4, 1, 0)) Then Sheets("bank").Cells(i, 7).Value = "Conciliated" Sheets("accounting").Cells(.Item(a(i, 2))(2), 7).Value = "Conciliated" End If End If Next End If Next End With End Sub
-
Re: Conciliation by reference and Amount
Hi jindon,
I was testing and works perfect. And very fast. It is what wished.
Excellent, very many thanks. Solved subject :thanx:
Until the next one!!! -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!