comparative

[sna]

Hi Dear,
I need your help to do comparation items in ReconcileTxn Sheet.The data retrieved from the internal system and some values in some columns contain some values and texts that's hard to reconcile.i need to reconcile LD type, Currency,Loan amount,rate,term,charge amount, repay type and customer.
Could you help provide a solution for this query?
I want to show OK if it matches otherwise check in column AB.
(NoteThe data in ReconcileTxn comes from Intranet and T24Intra Sheets)

Thank in Advance

Best wishes,

[snasui]

Could you please give some result and explain what condition to get it?

[sna]

Thanks .I want to return OK in Col.AB if SERIAL NUMBER,LDTYPE,CCY,LOAN AMOUNT,RATE,TERM,CHARGE AMT and REPAY
From Intranet and T24 data matching otherwise return Check.

I also need your help regarding macro in the three modules.I just learn vba

Best wishes,

[snasui]

I can't help you with your infomation in post #3 because it not enough for me to understand your requirement.

The better description when you reference to your data is column reference such as A, B, Z, etc. not just value in cell.

I need to know what the column address in three sheets is that you want to compare together.

[sna]

In ReconcileTxn needs to compare col F with Col Q,G with R,H with S,I with T,J with U,K with V,L with W and M with X.
Col C thru M got from Intranet Sheet
Col O thru X got from T24 intra

Best wishes

[snasui]

Try this,

AB13

=AND(F13&""=Q13&"",G13=R13,H13=S13,I13=T13,SUBSTITUTE(J13,"M","")=SUBSTITUTE(U13,"M",""),K13=V13,L13=W13,LEFT(M13,10)=LEFT(X13,10))

Enter > Copy down.

If the result is TRUE its means that all are equal.

[sna]

Thank you so much

[sna]

I also need your help related back up code in this workbook why it is not working fine?also clear data code located in the workbook?

Thanks

[snasui]

What the code that you refer to?

Please tell me about the result that you expected from that code.

[sna]

In the module,I need to copy from Reconcile Txn to back up but only data and the other two is to clear old data for intranet and t24 intra

[snasui]

Please give me a module name and procedure name that cause your problem.

[sna]

Module 1,2,3.
Once more in sheet T24 intra column K I want to return in ReconcileTxn col U only 60M etc not leading zero like in sheet T24...


Thanks

[sna]

Any solution???

[snasui]

What is the problem of Module 1 and Module 2?

[sna]

When run macro it is slow

[snasui]

In my machine it's not slow when run these codes.

You can set calculation to manual before run code and set to default after finished your task as below:

Code: Select all

Sub RCL2Clr()
    Dim rg As Range
    Application.Calculation = xlCalculationManual
    'Other code
    Application.Calculation = xlCalculationAutomatic
End Sub

[sna]

Thank you for your Input.
Now once more column J and U,Column K and V not agreement.How to fix this?
Note exchange rate
1USD=4,000 KHR
1THB=129.155 KHR

Best Regards,
I also attached a revised template

[snasui]

Adjust your formulas as below:

1. F13
   =IFERROR(INDEX(INTRANET!AI:AI,MATCH(ROW()-12,INTRANET!$AG:$AG,0))+0,"")
   Enter > Copy down
2. J13
   =IFERROR(SUBSTITUTE(INDEX(INTRANET!L:L,MATCH(ROW()-12,INTRANET!$AG:$AG,0)),"M","")+0,"")
   Enter > Copy down
3. Q13
   =IFERROR(INDEX('T24 INTRA'!N:N,MATCH(E13,'T24 INTRA'!$G:$G,0))+0,"")
   Enter > Copy down
4. U13
   =IFERROR(SUBSTITUTE(INDEX('T24 INTRA'!K:K,MATCH(E13,'T24 INTRA'!$G:$G,0)),"M","")+0,"")
   Enter > Copy down
5. AB13
   =AND(F13=Q13,G13=R13,H13=S13,I13=T13,J13=U13,ISNUMBER(SEARCH(V13*IF(R13="USD",4000,1),K13)),L13=W13,LEFT(M13,10)=LEFT(X13,10))
   Enter > Copy down

[sna]

Thanks this looks better but RATE and Charge Amt not matching.rate is annum and charge amount should be in KHR.
Exchange rate 1USD=4000 KHR,1THB=129.55 KHR.charge any in column V should follow column K.and rate in column I should follow column T.i mean column k is exchanged already and column T rate is in annum.

Hope you see and may help.

Best Wishes

[sna]

Note charge amt comes from charge code 7&8 added together but they sometimes mixed with column with charge code 2 if no charge code 2 so it is hard to trace