Excel: Subtotal to a new tab?

Factual Questions
1

Is there a way to take a raw data sheet and then tell Excel to subtotal column X at each change in B and that each change of B needs to go on a new tab? In other words, if my data contains 12 groupings of data, that Excel will automatically create a 12 new tabs and populate it with 1 group per tab?
The good ole’ copy and paste method gets old when a particular grouping contains, 16,000+ lines and the 12 groups contain 123k lines combined.

2

Getting a subtotal to a new tab is fairly easy using the PivotTable command in Excel.

Moving sub groups of 16,000+ lines to new tabs is a pain. Could probably be done fairly quickly with AutoFiltering. If it is something that needs to be done repeatedly and often, it is probably best to use other software or totally rethink what you are doing.

3

Sadly, the subgroups of 16000 lines are outside of my control. The data is dumped from the vendor as a .csv file. My raw data is 123,383 lines for November and will probably push 150k by the end of January. I am trying hard not to cry. On the upside, this version of data is accurate, unlike the data they were giving me that only ran 1000 lines but required an advanced degree in Twisted Logic to interpret.

I have an irrational hate for pivot tables, but it looks like that will be my best solution. Sigh.

4

Any hate for pivot tables has to be irrational, as they are one of the best features of Excel. Take a bit of time to figure out what they can do.

5

To do without pivot in 07 and later, copy the category range to a blank sheet. Use the remove duplicates feature (on the Data tab). For your sum formula use something like =sumif(sheet1!a:a,a:a,sheet1!b:b). If calculation times get annoyingly slow, convert your SUMIFs to values.

Syntax

SUMIF(range,criteria,sum_range)

Once you get up around 200k or 300k lines of data you want to migrate to a database, IMO, but you might have a better work computer.

6

mcgato - I understand that my hate is irrational. I think it is driven by people who try and put things in pivot tables that don’t need pivot tables so then I still have dig into the data to see what I need to see instead of what they put in the pivot table.

Dave - Why remove duplicates and SUMIF? I know my data doesn’t have duplicates. I am hoping by the time we get to that point the vendor will have cleaned up their data themselves to total by month instead for each and every hour within the month…

Thanks to you both.

7 

Region Sales
North 100
South 200
South 200
South 100
East 1000
East 100
West 600
West 400

Let’s say you want to subtotal Sales by Region using SUMIF. One way is to copy the Region column to another tab and remove the duplicates.


Region
North
South
East
West

Then use SUMIF to total Sales by comparing the criteria range (the original Region range) to the criteria (the unique regions) and adding up the sum range (Sales). You’re not removing duplicate rows, you’re determining unique criteria.

8

Thanks Dave. I misunderstood what you were saying earlier.

I logged into work to get the pivot table part set up. Now I have to figure out how to get the data on the new tab to sort the way I want. It will take some playing with the advanced sort to get it to line up the way I want (aka the way the data showed originally)