Solved

Excel 2007 Macro to break up data into multiple sheets based on criteria

Posted on 2010-08-30
6
677 Views
Last Modified: 2013-11-10
Hi,

I have data on an Excel 2007 spreadsheet that needs to be broken up into multiple spreadsheets, and further broken up on each spreadsheet based on criteria --  with a tiny bit of formatting to boot. If someone could help out, I would greatly appreciate it!

I have attached a sample file with the source and the desired result, but a bit of explanation and attention-drawing is required (all of this is demonstrated in the sample file, but you might miss it just by looking):

-- The data should be divided into separate sheets based on Category 1.
-- On each generated sheet, the data should be broken down into sections by Category 2, with the formatting the same as in the sample file (Category name in bold size 14, line skipped, headers in bold,data below, line skipped, category name in bold size 14, etc..)
-- The Fruit sections, which have no data in the Data 1 column, should not have that column on the result sheets.

-- IMPORTANT NOTE: Category 1 (which determines the sheets) has a category called 'All'. Data for the 'All' category should NOT get its own sheet, but should appear on EACH generated sheet, broken up by Category 2 appropriately.

Very grateful for any help!

Please let me know if the explanation or the sample file are unclear.

Thanks!

Andrey
Sample-Data-for-EE.xls
0
Comment
Question by:andreyman3d2k
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 33560444
Have a look at this. This should get you started.  It creates all the sheets and adds all the data to the sheets accordingly and then sorts it by Category 2.

I haven't gotten to the formatting yet, or removing the blank row.  The rest of it's in there though.  That's all I have time for right now, I can circle back later and see if there's any other posts.

Sample-Data-for-EE.xls
0
 
LVL 6

Author Comment

by:andreyman3d2k
ID: 33560718
Thanks, but unfortunately my VBA knowledge is not up to task to build on this.
It breaks the data up into sheets, but it:

-leaves the the Category columns in place
-leaves out the headers
-does not create category 2 as headers
-leaves the Data1 column for data that does not have anything in Data1

I can perform basic code modifications, but this beyond my cranium : )

Andrey
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33561524
OK, I've added in the formatting.

Try this.

Sample-Data-for-EE.xls
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 6

Author Comment

by:andreyman3d2k
ID: 33561883
Broomee, thanks, this is very close -- 3 things:

1. Would it be possible to not include the category 2 column in the final output, since it already becomes the header section?

2. For some reason, after running the macro, the NJ tab is fine, but on NY and CT I get a '3T' in the fruit header... like this:

Category 2      Data 2      Data 3      3T

3. Not all the data is carrying over -- CT lost the pumpkin record. Also, the first Fruit is vanishing --- (I think that's there the extra 3T is coming from)
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 33562214
OK, these changes should address the problems in your last comment.

Sample-Data-for-EE-3-.xls
0
 
LVL 6

Author Closing Comment

by:andreyman3d2k
ID: 33568300
Hey, this is great, thanks very much!

Andrey
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question