matching 2 files/records

[skaswani]

Dear Friends,


How are you all


I am using Excel 2002, with Windows XP,

Problem :-

I am working with a institution which details in remittance, what I need to do,

I get 2 reports daily,

One is Online System Generated (Format Enclosed in attachment with the file name : HO.xls” )

Other is the Branches email use the payments details (Format Enclosed in attachment with the file name : Branches.xls” )


What I need to do is,

Daily I download Transaction into HO.xls files, and the payments are notified by Branches when someone claim it.

I need to make some macro/procedure that the Branches Payments details records come columns should display/paste to HO.xls file (both files have common Ref. Nos.)


Columns from Branches include :- CINC No, Amount, and Payment Date (ref to cell # B6 should be printed to every transaction)



Please kindly help me in this regard,

Regards,

[Ago]

i have some trouble understanding what you want the macro to do.

but im guessing you want a script that opens Branches and looks in HO if the payment is done, correct?
what should happen if the payment is done? colour it? delete it?

[skaswani]

ya.. if the branch have the record , then CNIC , Beneficiary's CNIC No, Beneficiary Address:,Amount (Pkr), and date (which is in cell b6) should be printed/display to HO file

got it?
thanks,

[RonMcK]

What is the CNIC? (the remitter?)
Are all amounts stated in PKR?
Do you want to include a column for the ID of the branch that paid?
How many branches are there?
How are the various branch daily files distringuished from each other?
Do you want the macro to do all the branches automatically or to ask you for a branch to do and loop back for another until you tell it to stop?
How does the entry in the Status column (Paid/Not Paid) affect whether or not the macro works?
What do you want the macro to do if the branch ref no matches a HO ref no BUT one or more of the other fields (Remitter name, Beneficiary name, Amount, and/or Transaction Date) do not match (are not identical)?
What do you want the macro to do if it finds that the file has already been processed (i.e. the branch fields on the HO file for a transaction are already filled-in)?
Should processed transactions in the branch file be marked as 'done'?
What happens if a branch transaction can not be matched to a HO transaction?
Is the HO file new each day (just for the day) or is it cumulative for a period of time (week or month, for instance)?Thanks,

Ron
Orlando, FL

[skaswani]

What is the CNIC? (the remitter?)
Are all amounts stated in PKR?
Do you want to include a column for the ID of the branch that paid?
How does the entry in the Status column (Paid/Not Paid) affect whether or not the macro works?
What do you want the macro to do if the branch ref no matches a HO ref no BUT one or more of the other fields (Remitter name, Beneficiary name, Amount, and/or Transaction Date) do not match (are not identical)?Thanks,

Ron
Orlando, FL

What is the CNIC? (the remitter?)
its the Beneficiary's ID card #

Are all amounts stated in PKR?
Yes,

Do you want to include a column for the ID of the branch that paid?
Yes,

How does the entry in the Status column (Paid/Not Paid) affect whether or not the macro works?

leave that column ,


What do you want the macro to do if the branch ref no matches a HO ref no BUT one or more of the other fields (Remitter name, Beneficiary name, Amount, and/or Transaction Date) do not match (are not identical)?

it should match!

that is the Identification Key of that Transaction


regards,

[RonMcK]

... if the branch have the record , then CNIC , Beneficiary's CNIC No, Beneficiary Address:,Amount (Pkr), and date (which is in cell b6) should be printed/display to HO file


What is the CNIC? (the remitter?)

its the Beneficiary's ID card #

Okay.

Is the branch ID the number in cell A3 of branch.xls?


Thanks,

Ron

[skaswani]

yes, and payment date date is in B6

[RonMcK]

If B6 is the transaction date, what is the transaction date in column C (C11 & C12)?

[RonMcK]

Skaswani,

Accompanying are copies of your original files with a module added to HO.xls that when run, imports records from your Branches.xls file. I'm importing a bit more data than you requested but this is simply to facilitate your proofing the import.

The program does several more things than you asked for. I think you will find them helpful. First, I added column headings over the data that I am importing. And, the program pops up a message box with a summary of how many records were in the Home office and Branches files, and how many of them matched and were imported.

When the program is done running, it saves the HO and Branches files before presenting the updated HO file to you for your consideration.

Please let me know what you think about the program. I have some ideas for enhancing the program so you can maintain one master HO file and import multiple Branch Office files.

I want to thank Simon Lloyd, Malcolm MacKillop, and Mike Erikson for their help.

Regards,

Ron McKenzie
Orlando, FL