Organise Data on Indiviual Tabs Automatically

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
Hi,

I have an idea about how I want my spreadsheet to work but I'm just not sure about the best way to go about it. As I will be arranging large volumes of data, I would like to keep it as simple as possible and avoid too many IF Statements which can cause the file to get too big. Can you please let me know if you have any ideas or suggestions?

The spreadsheet will have a data tab, where all the raw data is downloaded to. I would imagine this would take up about 50,000 rows. This data will be organised by account number, which every row will have.

There will then be separate tabs for each client. Say, 30 clients. Each client is responsible for approximately 10 account numbers each. At the top of each client tab it will have a summary showing the totals for the account numbers. This I guess I'll get by SUMIF looking at the data tab.

The next part is where I'm a little unsure about what to do. Under the clients totals summary report, I want a breakdown to follow which shows all the transactions for that client. I could allocate a large enough section for each account number and then hide any blank/unused rows. It just needs to be in the order it is currently in on the data tab. But how can I get the data here easily? Basically, how can I get it to report just the account number transactions for that client, from looking at the many transactions on the data tab.

Here's an example..

Data Tab

Client ABC Tab
SUMMARY (SUMIF totals looking at Account Numbers on Data Tab)
$2,050.56 - Account No. 1
$1,009.25 - Acounnt No. 6
$5,555.90 - Account No. 25
$2,000.00 - Account No. 66
$3,250.85 - Account No. 104
NARRATIVE
All Account No. 1 transactions (=$2,050.56)
All Account No. 6 transactions (=$1,009.25)
All Account No. 25 transactions (=$5,555.90)
All Account No. 66 transactions (=$2,000.00)
All Account No. 104 transactions (=$3,250.85)

The person who will be using this has little Excel experience so I intend to automate everything as much as possible by putting in Macros/VBA coding.

Thanks for your time! :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top