Solved

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

Posted on 2010-08-30
6
674 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A short article about a problem I had getting the GPS LocationListener working.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now