Solved

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

Posted on 2010-08-30
6
678 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

752 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