Need VBA Reconciliation

[Monty]

Hello Everyone!

This is little tricky at the same time found interesting to reconcile two sheets and put the data in "Recon" Tab.

There are 3 Tabs:

Tab1 : MCR
Tab2 : IR
Tab3 : Recon

Step 1: Filter (MCR Tab) in C column with ESG_FLOW_USD and "G" column with 1USD AND 2USD.
Pick up first visible cell of "E" column that is "Fo_Trade_ID" and check in the "IR" tab if that exists if exists sum up "E" column and take the amount...no need to match the amount and put the same in "Recon" sheet the entry and also need to fill by the macro staus as matching and not matching.

I have give clear notes in attached file...tried to explain as easy as possible.

Really appreciate your help.

 

[NARAYANK991]

Hi ,

Some more explanation is required.

1. The relevant columns in the IR tab are column B and column E ; is this correct ?

2. The relevant columns in the MCR tab are column E and column F ; is this correct ?

3. The lookup is of the values in column E in the MCR tab into the range B2:B17 in the IR tab ; is this correct ?

4. The value 2320100NR occurs in column E , in row 6 , 11 and 28 , in the MCR tab ; which value of amount is to be considered for transfer to the Recon tab , and why ?

Narayan

 

[Monty]

Hi ,

Some more explanation is required.

1. The relevant columns in the IR tab are column B and column E ; is this correct ?

Yes the relevant columns are "B" and "E" columns
2. The relevant columns in the MCR tab are column E and column F ; is this correct ?

Yes the relevant columns are "E" and "F" columns

3. The lookup is of the values in column E in the MCR tab into the range B2:B17 in the IR tab ; is this correct ?
Yes

4. The value 2320100NR occurs in column E , in row 6 , 11 and 28 , in the MCR tab ; which value of amount is to be considered for transfer to the Recon tab , and why ?

Narayan

Here is my complete Snapshot of requirement...

MCR TAB

in MCR tab we have you have filtered data with "ESG_FLOW_USD" for coloumn "code" and in mapping column with 1USD AND 2 USD..This is what expected as first step.

This is how it looks like when you filter.

MCR TAB



IRTAB
Now what next : Take the first visible FO_Trade_ID in this case 2353387NR1 and check if this exists in the "IR" Tab...if exists then sum up PV column.

This is how it looks in IR TAB


Recon Tab
Now in the recon tab we have put the data as following



And this steps continues till the last available FO_TRADE_ID...
Please let me know any questions...if unable to put it across...Thanks

 

[NARAYANK991]

Hi ,

Question #4 is still unanswered.

Even after the filter is applied in column C , in MCR tab , there are 3 entries with the same value in column E viz. 2320100NR.

Which value of this should be entered in the Recon tab , and why ?

Do we always consider the first value , and ignore the other values ?

Narayan

 

[Monty]

That's okay two be three or more then be as mapping that is ( 1USD OR 2USD ) should also to be considered...So in this case it is only one entry.

 

[NARAYANK991]

Hi ,

OK. So only those entries in the MCR tab will be considered which have 1USD or 2USD in column G ; can a filter be applied for this also ?

Narayan

 

[Monty]

yes you are right sir...

I have applied two conditions only for MCR TAB "C" column should "ESG_FLOW_USD" and mapping column "G" column should be "1USD" OR "2USD" this is how it looks like after the conditions.

now need to take each "Fo_Trade_ID" from the "E" column and start reconsile with "IR"...good part is that we are not checking the amount is matching or not just check if the trade id exists in "IR" tab..if exists get that info to "Recon tab"

As per my attatched file...it shows clearly how the data is formed in recon tab from other tabs "IR" and "MCR" tabs....Thanks you...

 

[NARAYANK991]

Hi ,

The Recon tab has the last entry as 2258852NR1 ; from where has this come ?

Narayan

 

[Monty]

Yes Naryan sir..

That is from MCR tab..

I have not tried to explian the second part..which is simple to first part..

Here in MCR tab we filter with ESG_CIS_EUR and in Mapping column like 1EUR or 2EUR as highlighted.

 

[NARAYANK991]

Hi ,

OK. Please check back later.

Narayan

 

[Monty]

Thank you so very much...will be on line and waiting for your reply!

 

[Monty]

Sir...Here is the idea of getting data to Recon tab on click of a button.

 

[Monty]

Hello Narayana sir.

Finally done some thing with the code to populate data into recon sheet.
But still working on getting dynamic range in formulas as the data not going to be same.

Here is the attached file..

Public Const MCR_Sheet As String = "MCR"
Public Const IR_Sheet As String = "IR"
Public Const Recon_Sheet As String = "Recon"

Sub Ronnie_Monty()
Dim CopyRange As Range
Dim Mcr_lrow As Long
Dim Uname
'Filtering data with "ESG_FLOW_USD" in C column and "1USD" or "2USD" in "G" column
'Finding last row MCR Sheet
Uname = Environ("Username")
Mcr_lrow = Worksheets(MCR_Sheet).Range("A" & Rows.Count).End(xlUp).Row
  Worksheets(MCR_Sheet).AutoFilterMode = False
  Worksheets(MCR_Sheet).Range("A1").AutoFilter
  Worksheets(MCR_Sheet).Range("$A$1:$G$" & Mcr_lrow).AutoFilter Field:=3, Criteria1:="ESG_FLOW_USD"
  Worksheets(MCR_Sheet).Range("$A$1:$G$" & Mcr_lrow).AutoFilter Field:=7, Criteria1:="=1USD", Operator:=xlOr, Criteria2:="=2USD"

  'Copy and paste visible cells of TRADE id
  Set CopyRange = Worksheets(MCR_Sheet).Range("E2:E" & Mcr_lrow)
  CopyRange.SpecialCells(xlCellTypeVisible).Copy Worksheets(Recon_Sheet).Range("C2")
  
  'Copy and paste visible cells of Currency
  Set CopyRange = Worksheets(MCR_Sheet).Range("B2:B" & Mcr_lrow)
  CopyRange.SpecialCells(xlCellTypeVisible).Copy Worksheets(Recon_Sheet).Range("E2")

  'Copy and paste visible cells of Amount
  Set CopyRange = Worksheets(MCR_Sheet).Range("F2:F" & Mcr_lrow)
  CopyRange.SpecialCells(xlCellTypeVisible).Copy Worksheets(Recon_Sheet).Range("H2")

  Lastrow_Rec = Worksheets(Recon_Sheet).Range("C" & Rows.Count).End(xlUp).Row
  Worksheets(Recon_Sheet).Range("G2").FormulaR1C1 = "=SUMPRODUCT((IR!R2C2:R17C2=Recon!RC[-4])*(IR!R2C5:R17C5))"

  Range("I2").FormulaR1C1 = "=RC[-2]-RC[-1]"
  Range("G2").AutoFill Destination:=Range("G2:G" & Lastrow_Rec)
  Range("I2").AutoFill Destination:=Range("I2:I" & Lastrow_Rec)
  Range("F2").FormulaR1C1 = "=IF(ISNA(MATCH(RC[-3],IR!R2C2:R17C2,0)),""NO Match"",""Match"")"
  Range("F2").AutoFill Destination:=Range("F2:F" & Lastrow_Rec)
  Range("J2").Value = "Trade inst"
  Range("J2").AutoFill Destination:=Range("J2:J" & Lastrow_Rec)
  Range("k2").Value = Uname
  Range("k2").AutoFill Destination:=Range("k2:k" & Lastrow_Rec)
Call Get_Columns
  Calculate
End Sub

Sub Get_Columns()
Dim i, k As Integer
Dim Rec1, Rec2
Lastrow_Rec1 = Worksheets(Recon_Sheet).Range("C" & Rows.Count).End(xlUp).Row
Lastrow_Rec2 = Worksheets(IR_Sheet).Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To Lastrow_Rec1
    Rec1 = Worksheets(Recon_Sheet).Range("C" & i).Value
  
    For k = 2 To Lastrow_Rec2
        Rec2 = Worksheets(IR_Sheet).Range("B" & k).Value
      
            If InStr(Rec1, Rec2) <> 0 Then
              Worksheets(Recon_Sheet).Range("A" & i).Value = Worksheets(IR_Sheet).Range("A" & k).Value
              Worksheets(Recon_Sheet).Range("D" & i).Value = Worksheets(IR_Sheet).Range("C" & k).Value
              Worksheets(Recon_Sheet).Range("B" & i).Value = Worksheets(IR_Sheet).Range("F" & k).Value
            End If
    Next k
  
Next i

End Sub

 

[Monty]

i really appreciate if you can provide a better approach to complete the same ...this is what could able to write code after 2 days working,eating, drinking and sleeping with this file.

 

[Monty]

Working on if formula and Sum product used in the macro are fixed length need to make dynamic!

 

[Monty]

Hello Narayana.

Hope you are still working on this...i really appreciate to see your piece of code..which will be definitely a great help.

 

[Monty]

In order to make this below sumproduct range dynamic

=SUMPRODUCT((IR!$B$2:$B$17=Recon!C2)*(IR!$E$2:$E$17))

Tried this...But this does not work

=SUMPRODUCT((IR!B:B=Recon!C2)*(IR!E:E))

Any clues!

 

[NARAYANK991]

Hi ,

See if this is OK. Run the macro named Reconciliation.

Narayan

 

[Monty]

Narayana sir...thanks is really small word... excellent code...will come back with little twick s.....sorry to respond you late.