My Excel file is working slow calculating 2 threads with complex formula.

[ahsan.masood1980]

Hi Guys, please find the attached file which i have prepared, whenever i do any calculation it start computing the formula very slow and showing percentage calculating 2 threads.
Sheet is basically to reconcile expenses with bank.
One sheet is for expenses.
2, 3, 4, 5 are the banks.
Could any one please go through this sheet and help me to make it working very fast. Because when i put the large data into it let say 1,000 transactions, you can imagine how much time it will take to calculate the data. And its 168 KB file.
For example i have put some data into it for your understanding.
Every function should remain the same, except some changes in formulas.
I will be very much thankful for your favor.
Thanks in advance.

[Glenn Kennedy]

I am not at all surprised that it is slow!! One formula in one cell in one of the sheets is calculating 1,000,000 rows 15 times to get an answer.... No doubt it can be speeded up. However, a couple of questions.

Data are entered manually in the individual bank sheets. Then amounts are entered manually ontot he expenses sheet, again manually, and the rest populates. Why do you not get Excel to consolidate the sheets for you? Unless there is something going on that I have not picked up, you are essentially consolidating 4 sheets into one in a very complicated fashion. So, why are the amounts entered manually on Expenses?

[Glenn Kennedy]

I now have it working MUCH more quickly. I have NOT finished yet. For the moment, I have disabled all the hyperlinks.

1. What is the purpose of Expenses Helper 2?

2. How are receipts handled? This seems a complete mystery to me!!

3. I renamed the sheets. My brain turned to paneer with Bank 1 appearing on Sheet 2, and so on. This caused serious confusion!

4. What is meant to happen in Expenses column H? Even in your original attachment the formula is full of #REF errors.

Columns shaded orange have new/amended formulae.

[ahsan.masood1980]

Hi Glenn, thanks a lot for your concerns and many thanks for proceeding with the file. Actually this sheet has been designed as per conditions appeared time to time, that is why it has gone to complex level. I have gone through your file and amazed & glad that its really working very fast and fine. i must say your strategy to re design this sheet is impressive. But as you said its not finished yet, will be waiting for the final version.
Let me answer your questions for further clarity.

1. Helper column 2 in expenses is for the purpose to match common figure that is in the expenses sheet and also in any of multiple sheets 1,2,3 & 4, can be matched by giving option other than 1st match e,g 2 for second match 3 for third match and so on.
2. Receipts has to be worked in same manner as being worked in expenses sheet. But its coming on later stages. That is why issues related with receipts not being raised yet.
3. I apologized for this confusion rather changing their names starting from 1, 2 and so on i started from 2.
4. H is the column which includes the final figure dealing after getting converted foreign currency amounts. FX amount will be under G column and after conversion with FX rates it will be put under column H.
I hope to hear from you soon. Once again thanks a lot fro your efforts Glenn.

[ahsan.masood1980]

Also my question is that as you have brought all the bank PAYMENTS from sheet 1,2,3 & 4 on to expenses sheet highlighted in orange fields. The working in orange field would not be there in final version isn't it or will it be if it is necessary required to be there? kindly do as necessary required.

[Glenn Kennedy]

You ignored my FUNDAMENTAL question in my first reply. Your answers to some of my other questions were not clear. I will ask again, here, along with some others:

1. What is the purpose of this sheet? Do you want it simply to consolidate all of the expenses from Banks 1-4 on a single sheet? If it is more complicated than that, what else does it need to do?

2. How many banks?

3. How many rows (maximum) on each bank sheet?

4. Will receipts EVER appear on the EXISTING COLUMNS of the expenses sheet, or will they be elsewhere?

5. Why do you need the hyperlinks? A sheet number and row number would also identify them. The hyperlinks are volatile and will recalculate every time anything changes. They will slow up your sheet. Can I kill them?

6. Any helpers will be kept to a minimum. They can be in hidden columns, of course. Is that OK?

[kvsrinivasamurthy]

Pl See file.
I have changed all most all formulas.
I added 3 helper columns in Expenses Sheet. and one column in each Sheet(2,3,4,5).

1st Step
You use helper Column in sheets 2,3,4,5 and change in formulas as given in file.
I think it will speed up working will be very fast as my experience in the given file.

2nd step
If you still you feel speed is not sufficient do the changes in Expenses sheet.

Note that range given is 200000, change as required.

Your feed back is awaited.

[ahsan.masood1980]

I apologize for missing the information. Please note my following answers to your questions.
1. Basically this is the file from which i want to reconcile expenses with the bank files. Its not bound to limit expenses or bank transactions, it may vary like expenses can be 200 and bank transactions in thousands. And expenses can be thousands. The main function is to reconcile the expenses with bank on single sheet which is "Expenses".
2. Bank sheet can be 4 or maximum 5 banks.
3. Bank records can be maximum 6,000 rows in each sheet it can be vary.
4. No, receipts does not have any function related to expenses.
5. Hyper links are meant to access reconciled records with just one click rather going to sheet and search it. Yes please you can kill it if it is necessarily required to do. If you think we can keep working with the links active its a suitable option for me to work with them.
6. Yes please.


Glenn i have tried to work with amended sheet. But there is something i have tried to work out and its has given the following results.
1. I have tried to put the figure in expenses sheet which is not in bank sheet has led to error #Ref.
2. i have put the common figures in multiple figures which led to error like e.g i have put the figure 1510 in bank 4 and in bank 5 repeated 3 times. But in expenses file it has detected first instance from bank 4 Ref: 1B3 and detected from bank 5 Ref: 2B4 rather detecting 1510 Ref: 1B4 as second instance. The reason i have found is that the payment helper "column "P" in bank sheet 4 & 5 ref: 1B3 & 1B4 are giving same references 1510_1. I also have observed that in expenses tab helper2 column is not changing according to common figures 1st, 2nd or 3rd match like helper1 column.
I don't know if this will help to get this sheet working in desired manner.
Column "M" in expenses sheet is meant to detect multiple common figures by putting figure 1, 2, 3 and so on for 1st match, 2nd match, 3rd match and so on.

Could you please look into these issues and help to resolve them.
Thanks.

[Glenn Kennedy]

Who are you talking to? Your comment was addressed to me, your file attachment was Kvsrinivasamurthy's. If you are going with his approach; I will step back at this point, and let him continue, otherwise huge confusion is going to result!!

[ahsan.masood1980]

I am so sorry Glenn i though it was you, while replying i have overlooked the post name as its a open forum. I apologize for making this confusion. I have considered your file & Please continue working on it.

Please find the following replies to your answers.

I apologize for missing the information. Please note my following answers to your questions.
1. Basically this is the file from which i want to reconcile expenses with the bank files. Its not bound to limit expenses or bank transactions, it may vary like expenses can be 200 and bank transactions in thousands. And expenses can be thousands. The main function is to reconcile the expenses with bank on single sheet which is "Expenses".
2. Bank sheet can be 4 or maximum 5 banks.
3. Bank records can be maximum 6,000 rows in each sheet it can be vary.
4. No, receipts does not have any function related to expenses.
5. Hyper links are meant to access reconciled records with just one click rather going to sheet and search it. Yes please you can kill it if it is necessarily required to do. If you think we can keep working with the links active its a suitable option for me to work with them.
6. Yes please.

Once again thanks.

[Glenn Kennedy]

Hahaha. But... you still haven't answered one very fundamental q. I will try rephrasing it.

Do you want a file that consolidates ALL expenditure from ALL 4 Banks into ONE sheet?

[ahsan.masood1980]

No i don't want to consolidate all the bank expenditures in to one sheet. I want all the bank in separate sheets.
Its like a sheet "Expenses" which reconcile only those expenses with the bank which i am going to use in the expenses sheet.
For example if Mr. A purchased a product on 01-02-2020 and the payment he made from bank 2,3,4 or 5 has hit the bank on 02-02-2020. So, the only purchase which i have entered in expense file will reconcile with bank transaction and showing on expenses sheet from which bank its has been reconciled.

Thanks Glenn.

[Glenn Kennedy]

OK. Version 2. Play with it and see if it does what you want it to do. If it's broken, I will try to fix it. If it's OK, then I will take a look at the formulae again and set it up for 6000 rows per bank sheet.

Take your time and test it thoroughly.

Two more Q's

1. There are some random-looking bits of VBA code in the sheet. Are they required?

2. Are the remaining helpers OK where they are, or would you prefer them to be on another sheet (which can be hidden)?

[ahsan.masood1980]

Ok Glenn thanks a lot. i will check it out and come back to you after testing the results of the file.
1. VBA can be removed, it was related to some other un-necessary files which has been deleted later on.
2. Those can be related to some deleted files, i am not sure let me just look into it and let you know.

Once again thanks for your efforts. See you again.

[Glenn Kennedy]

I've just noticed you're in Islamabad. I visited your city 3 times in the space of 6 weeks in 2016. One meeting turned into three, in rapid succession!!!

It's a well planned and green city ...

[ahsan.masood1980]

Yes, its been almost 4 years past. Now its much more developed than before ,especially they have launched the tourist bus services to move around all of the place in Islamabad. You are welcome to plan a head in the future to visit Islamabad.

[Glenn Kennedy]

Hahaha. Not allowed to travel anywhere at the moment, thanks the The Virus....

[ahsan.masood1980]

Yes i can understand its everywhere around in the world. Due to COVID-19 everybody are get stuck up to their necks.
Anyways, It will be our pleasure whenever you will visit our place.
Its really pleasure talking to you and for your help. I appreciate a lot for your dedication. Really person like you makes the world more easier place to live.
CheerzzZzz.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[ahsan.masood1980]

Hi Glenn i have already done that the time when you launched the solution file.
Thanks.