Greetings from Atlanta, GA. I have been a student of Chandoo for several years and have benefited greatly from learning techniques and tricks from this website. While my Excel skills may seem advanced to some, there are a few things I just have no clue how to start and the reality is that I know nothing compared to the people I meet on this forum. I would like Excel to take a gigantic data file and bust the data up based on words contained in the data? For example, one of the columns of data has the name of a customer and then there are 5000 rows of data with that customer name in it. I would like to have Excel bust the data up onto separate worksheets for each customer. Is that even possible? If it is what class do I need to take to be able to learn how to do it? For example, my data file has 110,000 lines of data that pertain to about 50 different customers.
Can Excel Do This?
- Thread starter Gail
- Start date
Peter Bartholomew
Well-Known Member
Filtering a table and generating a new sheet of data is something that Power Query can do easily, though automating the process for 50 customers might require deeper knowledge of the M language.
Hany Ali, thank you for responding. I didn't understand your message except for the fact that I needed to upload the file. I tried to upload it and it gave me an error message that the file was too big. It is 7.2MBs. So I cut out half of the data (now only 50,000 lines as a test - 3.4MBs) and it was still too big to send through the forum.
Peter, I see the value in the Power Query and much of the "M" language is perfect common sense if you have some working knowledge of Excel. I basically was able to create a listing of the customers with all of their data aggregated on a table literally in a matter of seconds. Tremendously helpful!!!!!! Thank you. I will continue working with it to see what else it can do for me. Right now I am trying to get the tables out of the Query!
Peter, I see the value in the Power Query and much of the "M" language is perfect common sense if you have some working knowledge of Excel. I basically was able to create a listing of the customers with all of their data aggregated on a table literally in a matter of seconds. Tremendously helpful!!!!!! Thank you. I will continue working with it to see what else it can do for me. Right now I am trying to get the tables out of the Query!
Gail
Some questions:
Why do You would like to have about 50 same layout sheets?
... if You'll need to modify layout of sheets, then You gotta do it ~50 times.
... how many sheets can You see in one time? ... normally one sheet?
Do You delete that original 110,000 lines data after separation?
... or You'll have 220,000 lines data and double size file...
Hmm?
If You could use FILTER to see one time one (or more customers) from Your original sheet
... then You'll need to take care one layout
... You could see all from one Your original sheet
... file size would keep same
Some questions:
Why do You would like to have about 50 same layout sheets?
... if You'll need to modify layout of sheets, then You gotta do it ~50 times.
... how many sheets can You see in one time? ... normally one sheet?
Do You delete that original 110,000 lines data after separation?
... or You'll have 220,000 lines data and double size file...
Hmm?
If You could use FILTER to see one time one (or more customers) from Your original sheet
... then You'll need to take care one layout
... You could see all from one Your original sheet
... file size would keep same
GraH - Guido
Well-Known Member
If possible with the data, "bursting" is possible with a simple pivot (tabular lay out to come as close as possible to a table lay out). Putting the name in the filter section and generating Report Filter Pages.
I need to create a separate worksheet for each of the customers to send with their invoice. I like the idea of using a pivot. Using your snip example, will I have to do that 50 times to create separate worksheets for each customer?
I have to create separate worksheets for each of the customers to send to the customers with their invoice, the so-called "billing detail". I want to be able to automate the bursting up of the data onto the separate worksheets to the extent that I can. It will save me a lot of time.
Hi vletm: The file is too big to attach unfortunately. Here are the details: 110,000 lines of data, all lines contain the customer name for the data. There are 11 columns with either numbers or text. I think you are correct about using a macro but I don't know how to do it. What can I read to get started?
Gail
Could You create a SAMPLE Excel-file?
... which has eg data from three customer (customer names could change to A...C)
... each customer's data should LOOK same as You're using ... and there could be eg 15 rows per customer
... of course, those data should use for ... the so-called "billing detail" ... too, which seems to be Your next step.
After, I've that a SAMPLE file, I could make more questions ...
Could You create a SAMPLE Excel-file?
... which has eg data from three customer (customer names could change to A...C)
... each customer's data should LOOK same as You're using ... and there could be eg 15 rows per customer
... of course, those data should use for ... the so-called "billing detail" ... too, which seems to be Your next step.
After, I've that a SAMPLE file, I could make more questions ...
GraH - Guido
Well-Known Member
The pivot does that for you. However reading further it seems you want to generate a workbook with billing details for each customer. Not just a separate sheet in the same workbook (you don't want all customers to see the details of each). Then follow Vletm's suggestion and go with a macro.
GraH - Guido
Well-Known Member
Don't see how Power Query on its' own will automate this. You will require a macro to script it. As far as my knowledge goes, the macro without using the query technique will be much faster. I've once replaced a complexer macro with a very easy one to do just this. And run time was about 30x slower. I learned later I could have used Table.Buffer(Table) in my M-code to speed it up.
So if you happen to know the technique that automates this without a macro, @Peter, I'm very much interested to learn how.