Large Pivot Tables with Userforms and LARGE sheets

AnvarJay

New Member
Joined
Jun 21, 2021
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Hi everyone,
Using Excel 2007 and 2010, both on PCs with Windows 10.
I used to play with userforms in Excel with VBA programming many eons ago and have had to come back to it to create a database style sheet system like what MS Works does for the Manufacturing business I work for. As in truth we need simple to a degree but not the expense of someone setting up a whole new database program CRM style from scratch.

Creating the sheet and userform was rather easy with just going through what I remember and reading alot of posts and comments on here and thanks to you all for that being nice and easy.

Well, now I am the hard part. I need some reports for the bosses, that within Works was actually really easy to create and do as it works well and simple, but in Excel well Pivot Tables are not my friend to say the least. We are about to change to newer systems and everything and Works is now so out of date we decided to just use Excel due to my previous experience with it.

Simply, we have a spreadsheet called CustData, which holds approx 49000 row for the last 20 years, and we need different reports to do different things with the data over the last 20 years and that is just too much data for a pivot table to work with. It can not be more limited as some work with historical rather than newer data. Some I have been able to just use it to pull set info and create a page that puts the info on their to print and then deletes and that is fine for them but not all.

The problem I am on at the moment with the data bieng so large, I can, on some cut down the data to use by changing data source, but that wont work as within the new source their is NO headers for the columns. For example, the sheet has 49000 rons and 54 columns. For this one report I am concentrating on it, so it can use just rows 45000-49000 and all of the columns but the headers are at Row1 so it doesnt like that.

The final print may only use approx 10 columns and about 20 rows of all that last 5000 rows, but it needs to pull the data from all those last 5000 rows to get the small data set that i then need to print. The old reports system in Excel years ago was better.

Is there a simple way to do that if it makes sense, I hope I have included enough info.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,632
Office Version
  1. 2007
Platform
  1. Windows
Welcome to the MrExcel Message Board!
I hope I have included enough info.
Too much information but I think the most significant was missing.
the sheet has 49000 rons and 54 columns.
I understand that you have a large database, and I assume that you want to extract some information that meets some criteria.
That is the core part, what are the criteria for extracting the information, examples of those criteria, which columns must meet those criteria.
The final print may only use approx 10 columns and about 20 rows
And the most important part, I think, the result, which columns you want to extract, where you want the result and how you want it.

You can put samples of your data here, what you have in your database, and what you want as a result.
Use XL2BB minisheets.

If you have confidential information, replace it with generic data. It is not necessary that you put 50,000 records here, just a sample where we can see which records do meet the criteria and which records do not meet the criteria.
And obviously the records that meet the criteria, you will have to show them in the output example.
 

AnvarJay

New Member
Joined
Jun 21, 2021
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Too much information but I think the most significant was missing.

I understand that you have a large database, and I assume that you want to extract some information that meets some criteria.
That is the core part, what are the criteria for extracting the information, examples of those criteria, which columns must meet those criteria.

And the most important part, I think, the result, which columns you want to extract, where you want the result and how you want it.

You can put samples of your data here, what you have in your database, and what you want as a result.
Use XL2BB minisheets.

If you have confidential information, replace it with generic data. It is not necessary that you put 50,000 records here, just a sample where we can see which records do meet the criteria and which records do not meet the criteria.
And obviously the records that meet the criteria, you will have to show them in the output example.
actually it had enough info for the question asked but thanks for trying. Prob has been solved externally.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,549

ADVERTISEMENT

Prob has been solved externally.
Glad to hear that it has been solved. If you could share your solution, then it is perfectly fine to mark your post as the solution to help future readers.

Otherwise, please do not mark a post as a solution that doesn't contain a solution.
 

AnvarJay

New Member
Joined
Jun 21, 2021
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Glad to hear that it has been solved. If you could share your solution, then it is perfectly fine to mark your post as the solution to help future readers.

Otherwise, please do not mark a post as a solution that doesn't contain a solution.
sorry i thought the solution button was for no matter where i found a solution so others new it had been solved for what i needed.
 

AnvarJay

New Member
Joined
Jun 21, 2021
Messages
17
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

It would be nice if you shared the solution...
actually from my own research and other people and other sites there was no answer for the version i was using in general other than to split the data.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,549
Thanks for the reply.

The solution button is not a "solved" indicator only, but to mark the solution post that answers the question. Splitting data is not the best solution in your project as I can see, but you could still mark your last post that mentions "split the data" as the solution since it is also an answer no matter it is the desirable one or not.

Also, just as an idea, if I was in a similar situation with that much data that I need to filter easily to create some reports, then I would use this worksheet as the data source and use DAO/ADO in VBA to fetch data quickly to create any report that I need.
 

AnvarJay

New Member
Joined
Jun 21, 2021
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Thanks for the reply.

The solution button is not a "solved" indicator only, but to mark the solution post that answers the question. Splitting data is not the best solution in your project as I can see, but you could still mark your last post that mentions "split the data" as the solution since it is also an answer no matter it is the desirable one or not.

Also, just as an idea, if I was in a similar situation with that much data that I need to filter easily to create some reports, then I would use this worksheet as the data source and use DAO/ADO in VBA to fetch data quickly to create any report that I need.
at the time with my knowledge (which of programming in vba, access and others) is all about 28 years ago so i didnt want to try that avenue as would have taken me too long to get my head around it all fully but thanks
 

Forum statistics

Threads
1,141,770
Messages
5,708,436
Members
421,570
Latest member
BaileyJ

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
Top