Group a table by category and put category in its own row? (without using VBA)

Bookender

New Member
Joined
Sep 18, 2013
Messages
47
First of all, my apologies if this may seem like a simple question, but this has bugged me for close to two weeks now, and I cannot get this to work the way I want it to. Also, my apologies if this has been answered before, but I haven't been able to find a working solution so far and may just have missed that other thread.


Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).

CategoryGenreTitleAuthorYear
Non-fictionPhysicsRadioactivityMs. Curie1922
FictionFantasyDragons!Hugh Mongus1539
FictionMysteryMurder!E. Nigma2014
Non-fictonGeologyYou RockP. A. Rody1875

<tbody>
</tbody>


And what I want, is basically a way to split this information out so that each category will stand seperate, with the other information being kept together by said category. As per below.

CategoryGenreTitleAuthorYear
Fiction
FantasyDragons!Hugh Mongus1539
MysteryMurder!E. Nigma2014
Non-fiction
GeologyYou RockP. A. Rody1875
PhysicsRadioactivityMs. Curie1922

<tbody>
</tbody>

I've already gotten the whole thing sorted into the categories with an alphabetising array, but what's causing me a headache is trying to get this whole thing set up so that it will give each category heading its own row, without using VBA (due to security settings - this is for an office computer)...

I can use as many helper columns as I need, I just can't use VBA. And avoiding INDIRECT() and other very volatile commands, too, would be nice, as the entire spreadsheet is static until someone adds data to the main sheet (which happens, perhaps, once a month). Any ideas among you brilliant people?





And before you suggest to add blank rows between each, shift everything but the categories one cell down, and then remove the blank spaces with an array, then each of these cells contain formulas - I cannot add blank rows by hand (would take DAYS!), and trying to copy-paste the formulas while skipping every other row results in... odd things happening to the formulas, as seen below.
So, if that would be the only solution, then I would need some help in adding the blank rows instead.

AB
1=A2
2
3=A3
4
5=A4
6
7=A7
8
9=A8

<tbody>
</tbody>
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
Why not just sort on column B (genre)?

Of course you need to select all data, before you sort.
 

Bookender

New Member
Joined
Sep 18, 2013
Messages
47
Sorting by Genre wouldn't do me much good if I want to get the Category into its own row, however. My alphabetising array already sorts the genres by letter, too, so that is not my issue. The issue is to, well, seperate the Categories from the rest of the data.

I can work with something that plugs the categories in as heading for each 'block', too. Just SOMETHING that lets me filter out the category into its own rows.
Fiction
FantasyDragons!
MysteryMurder!
Non-Fiction
GeologyYou Rock
PhysicsRadioactivity

<TBODY>
</TBODY>
 

Forum statistics

Threads
1,082,573
Messages
5,366,372
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top