Large Pivot Tables with Userforms and LARGE sheets

[AnvarJay]

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.

 

[DanteAmor]

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]

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.

 

[RoryA]

Prob has been solved externally

It would be nice if you shared the solution...

 

[smozgur]

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]

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]

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]

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]

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