Join 3,418 readers in helping fund MetaFilter (Hide)


How can I make excel break multiple rows into new sheets without doing it manually?
November 13, 2012 9:44 AM   Subscribe

How can I make excel break each common row off into a new sheet?

My google-fu doth fail me.

I have an excel sheet which is something like this

Column1
APPLES
APPLES
APPLES
BANANAS
BANANAS
BANANAS
CARROTS, etc

How do I get Excel to break the categories into seperate sheets without doing in manually (it's a very long list)?

Bonus Points: There are some things which are similar but not exactly the same which should stay together--ex. BANANAS, BANANAS1--is there any way to keep them together on a new sheet?

Thanks!
posted by Calicatt to Computers & Internet (10 answers total) 1 user marked this as a favorite
 
One method is to do a pivot table.

Put Fruit in the Row Labels, this will consolidate each fruit into one line.

Put if you don't have an amount next to the fruit to sum, put a column in with an X in each cell, and then call that heading Count. Put it in the Values area.

Now, to break out each fruit by page, double click on the count within the pivot table, and a separate sheet will be created. Do this for each fruit, et voila!

They're now sorted into separate sheets.
posted by Ruthless Bunny at 10:18 AM on November 13, 2012


if you don't have an amount next to the fruit to sum, put a column in with an X in each cell, and then call that heading Count. Put it in the Values area.

You actually don't need to do this -- You can drag Fruit in the Row Labels area and also into the Values area and it will give you a count of Fruit as your values.
posted by brainmouse at 10:25 AM on November 13, 2012


Thanks for the tips!

I've had some limited success with pivot tables in the past, but avoided them in this case since they always end up looking all...pivot tabley. Which doesn't matter if you're printing it, but this is a file that has to be passed from me to another person who will break the seperate sheets into seperate files which will be posted for public viewing. So the end result has to be as straightforward-looking as possible.

I inherited this process from someone else, and it just seems way more complicated than it needs to be: they were cutting and pasting each "fruit" and each "sub-fruit" into a pre-made template document with each "fruit" already set up as a seperate sheet (with further breakdowns on each sheet). It's one of those things that makes me feel there HAS to be a better way!

If I were to use a pivot table is there a way to save it so that it doesn't look like I used one (so as to not confuse the person I'm passing this to/the general public)? Or is there another way?
posted by Calicatt at 10:47 AM on November 13, 2012


And if it helps, this is actually a long list of books, so no numbers or functions.
posted by Calicatt at 10:49 AM on November 13, 2012


You don't have to share the pivot table part of the spreadsheet with the rest of the world. You can copy and paste each individual sheet into a separate spreadsheet, or you can hid the pivot sheet and the raw data sheet.
posted by Ruthless Bunny at 10:53 AM on November 13, 2012


Maybe use the Subtotal function? You can put a new page break at a change in whatever column you deem appropriate (like Column A).
posted by parilous at 12:34 PM on November 13, 2012


Wow, I didn't know about double clicking on the data items in pivot tables to create a new sheet with that data. That's cool.

I feel like to totally automate this you would need to write a VBA macro - basically iterate down each row and compare it with the row above and when you get to one where the value changes, copy the relevant range of cells into a new sheet. Unfortunately I don't know enough VB to walk you through that.
posted by yarrow at 12:57 PM on November 13, 2012


Wow, I didn't know about double clicking on the data items in pivot tables to create a new sheet with that data. That's cool.

Ain't it?

I'm nudging the edge of VBA myself, but I sort of draw the line at programing. I have all kinds of jankity ways of manipulating Excel data.

I use a combination of a Pivot Table and VLOOKUP tables to organize my data all on the same lines.
posted by Ruthless Bunny at 1:34 PM on November 13, 2012


Ozgrid usually is my, how do I try that in excel. Basic Filter, by unique values and copies into a new worksheet naming the worksheet what it found. 20 different names with dynamic amounts per name. For the same as Bananas and Bananas 1 you will get different pages for each Bananas. That would just require a sort and replace.

Look for the solution file.
http://www.ozgrid.com/forum/showthread.php?t=70307
posted by brent at 8:20 PM on November 13, 2012


Thanks! I'll give these a try
posted by Calicatt at 5:55 AM on November 14, 2012


« Older What are some good techniques ...   |  Yesterday I added a network pr... Newer »
This thread is closed to new comments.