Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • Last Modified:

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

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
andreyman3d2k
Asked:
andreyman3d2k
  • 3
  • 3
1 Solution
 
TracyVBA DeveloperCommented:
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
 
andreyman3d2kAuthor Commented:
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
 
TracyVBA DeveloperCommented:
OK, I've added in the formatting.

Try this.

Sample-Data-for-EE.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
andreyman3d2kAuthor Commented:
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
 
TracyVBA DeveloperCommented:
OK, these changes should address the problems in your last comment.

Sample-Data-for-EE-3-.xls
0
 
andreyman3d2kAuthor Commented:
Hey, this is great, thanks very much!

Andrey
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now