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).
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Genre[/TD]
[TD]Title[/TD]
[TD]Author[/TD]
[TD]Year
[/TD]
[/TR]
[TR]
[TD]Non-fiction[/TD]
[TD]Physics[/TD]
[TD]Radioactivity[/TD]
[TD]Ms. Curie[/TD]
[TD]1922[/TD]
[/TR]
[TR]
[TD]Fiction[/TD]
[TD]Fantasy[/TD]
[TD]Dragons![/TD]
[TD]Hugh Mongus[/TD]
[TD]1539[/TD]
[/TR]
[TR]
[TD]Fiction[/TD]
[TD]Mystery[/TD]
[TD]Murder![/TD]
[TD]E. Nigma[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Non-ficton[/TD]
[TD]Geology[/TD]
[TD]You Rock[/TD]
[TD]P. A. Rody[/TD]
[TD]1875
[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Genre[/TD]
[TD]Title[/TD]
[TD]Author[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]Fiction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fantasy[/TD]
[TD]Dragons![/TD]
[TD]Hugh Mongus[/TD]
[TD]1539[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mystery[/TD]
[TD]Murder![/TD]
[TD]E. Nigma[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Non-fiction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Geology[/TD]
[TD]You Rock[/TD]
[TD]P. A. Rody[/TD]
[TD]1875[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Physics[/TD]
[TD]Radioactivity[/TD]
[TD]Ms. Curie[/TD]
[TD]1922[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=A2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=A3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=A4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=A7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]=A8[/TD]
[/TR]
</tbody>[/TABLE]
Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Genre[/TD]
[TD]Title[/TD]
[TD]Author[/TD]
[TD]Year
[/TD]
[/TR]
[TR]
[TD]Non-fiction[/TD]
[TD]Physics[/TD]
[TD]Radioactivity[/TD]
[TD]Ms. Curie[/TD]
[TD]1922[/TD]
[/TR]
[TR]
[TD]Fiction[/TD]
[TD]Fantasy[/TD]
[TD]Dragons![/TD]
[TD]Hugh Mongus[/TD]
[TD]1539[/TD]
[/TR]
[TR]
[TD]Fiction[/TD]
[TD]Mystery[/TD]
[TD]Murder![/TD]
[TD]E. Nigma[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Non-ficton[/TD]
[TD]Geology[/TD]
[TD]You Rock[/TD]
[TD]P. A. Rody[/TD]
[TD]1875
[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Genre[/TD]
[TD]Title[/TD]
[TD]Author[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]Fiction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fantasy[/TD]
[TD]Dragons![/TD]
[TD]Hugh Mongus[/TD]
[TD]1539[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mystery[/TD]
[TD]Murder![/TD]
[TD]E. Nigma[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Non-fiction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Geology[/TD]
[TD]You Rock[/TD]
[TD]P. A. Rody[/TD]
[TD]1875[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Physics[/TD]
[TD]Radioactivity[/TD]
[TD]Ms. Curie[/TD]
[TD]1922[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=A2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=A3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=A4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=A7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]=A8[/TD]
[/TR]
</tbody>[/TABLE]