Macro to copy and paste data and update formula

[Aoleone#]

Hi Guys

I'm trying to improve a piece of reconciliation of two data sets( Attached), one is from an online Subscription purchase portal and the other is the Payment API for processing the payments. Transactions on both data is suppose to match using the Financial Ref(FREF).So far I copy the (DATE, FREF , and AMOUNT) details for both datasets from the data download and apply formulas to reconcile or match the transactions. However, the data has over a short period become monstrous and copying and pasting is becoming ineffective making my excel crawl virtually. Grateful if anyone could help with a macro to paste the data directly from the download and update formulas. The main idea is to match the customer purchases to the payments API transmission to ensure all payments have been received and payment processed.

Thanks in advance for your help


Aoleone

[dflak]

I think I can help out. In fact, I may be able to automate more of this than you asked for. First a couple of questions:

Do you first copy and paste data into the Online Purch Portal Download and Payment API Download sheets and then repaste the onto the Reconciliation Sheet?

I'll assume that these are from separate spreadsheets given to you as a report. Can you get direct data access to the database that generates these reports?

The solution I will offer uses Excel Tables to limit the amount of data you have to look at. It will also turn off calculations while the copying is going on.

I suggest reading up on them while I work the issue: http://www.utteraccess.com/wiki/inde...ables_in_Excel

[dflak]

Here is my first shot at it.

I did a couple of things to speed up the process. The first was to turn off calculations until after all the copying was done.

The second was to convert the information into Excel Tables. Among other things, Excel tables allow you to reference exactly the amount of data you need rather than a whole row. Excel tables "know" how many rows they have. It also made the coding easier.

Finally, I replaced your COUNTIF formula with MATCH. Match runs a lot faster than COUNTIF.

[Aoleone#]

Thank you very very much dflak. This reconciliation has been doing my head in for the past two weeks. To answer your initial couple of questions. I download( in excel format) both data from a back office application then copy and paste into the Online Purch Portal Download and Payment API Download sheets and then repaste them onto the Reconciliation Sheet. The only access I've got is the ability to download from the back office application.

My other issue is to be able to separate the corporate the subscriptions( with lower character "c" at the end of the FREF) into the third table in the reconciliation sheet, so that the first table will only be the non-corporate subscriptions.

So far I'm loving it!! Thanks once again.