Splitting data out into 100s of worksheets

[ianjones1990]

[FONT=&quot]Hi there,[/FONT]
[FONT=&quot]I have been asked by someone in my organisation to try and improve an excel file they use for managing account transactions.[/FONT]
[FONT=&quot]They basic format is this:[/FONT]

1. We generate a tab delimited TXT file from our banking portal which lists all of the transactions made for the month.
2. This is then pasted into a worksheet called "All Transactions".
3. We have approx. 160 other worksheets (1 per account) which uses VLOOKUPs to get transactions only related to that account.
4. The starting balance of the account is entered on each worksheet and then each transaction is added/subtracted from this balance, giving the new balance.

[FONT=&quot]The performance is very poor on this and it can cause the users machine to lock up. Calculations take up to 5 minutes when new data is pasted in. I have tried replacing VLOOKUPS with INDEX and MATCH but have noticed no improvement.[/FONT]
[FONT=&quot]Can excel handle this in a better way? I feel like pivot tables could be useful here but I'm not sure how you could handle subtraction and addition to the account balance using them.[/FONT]
[FONT=&quot]Any advice would be greatly appreciated.[/FONT]

 

[farmerscott]

Hi,

Welcome to the forum.

1. Can you post a (non commercially sensitive) copy of your data, for your "All Transactions" sheet.

2. What do you want on each sheet (each transaction, balance, etc)? Do you want sheets/accounts with no transactions to be shown?

3. Are all sheets present in the workbook or do they need to be created?

4. how do you want the data presented on each sheet?

thx.

 

[Joe4]

Cross-posted: https://www.excelforum.com/excel-general/1232426-splitting-data-out-into-100s-of-worksheets.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

If you have posted it to any other sites than the one I listed above, please provide links to those threads as well.