[h=2]Indented Headings in single column - need to extract to multiple columns[/h]
<ins id="aswift_2_expand" style="text-decoration: none; display: inline-table; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;"><ins id="aswift_2_anchor" style="text-decoration: none; display: block; border: none; height: 250px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 300px; background-color: transparent;">******** width="300" height="250" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_2" name="aswift_2" style="left: 0px; position: absolute; top: 0px;">*********></ins></ins>
I have a spreadsheet which pulls in data, however the headings and sub-headings come in the same column i.e. A and are indented, for example:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[/TR]
[TR]
[TD] England[/TD]
[/TR]
[TR]
[TD] Apple[/TD]
[/TR]
[TR]
[TD] Brand A[/TD]
[/TR]
[TR]
[TD] Brand B[/TD]
[/TR]
[TR]
[TD] Pear[/TD]
[/TR]
[TR]
[TD] Brand A[/TD]
[/TR]
[TR]
[TD] Brand B[/TD]
[/TR]
[TR]
[TD] France[/TD]
[/TR]
[TR]
[TD] Apple[/TD]
[/TR]
[TR]
[TD] Brand A[/TD]
[/TR]
[TR]
[TD] Brand B[/TD]
[/TR]
[TR]
[TD] Spain[/TD]
[/TR]
[TR]
[TD] Strawberry[/TD]
[/TR]
[TR]
[TD] Brand A [/TD]
[/TR]
</tbody>[/TABLE]
I need to display this indented data in three separate columns
[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Fruit[/TD]
[TD]Brand[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Apple[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Apple[/TD]
[TD]Brand B[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Pear[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Pear[/TD]
[TD]Brand B[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Apple[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Apple[/TD]
[TD]Brand B[/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD]Strawberry[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This will allow me to pivot if required. For example heading 1 is country and this is indented by 1 space, heading 2 is Produce and this is indented by 3 spaces, heading type is indented by 5 spaces.
Is there a formula I can use to keep the original indented column data but also view in a a tabular format.
Happy to send over an example Excel file.
Thanks,
Brett
I have a spreadsheet which pulls in data, however the headings and sub-headings come in the same column i.e. A and are indented, for example:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[/TR]
[TR]
[TD] England[/TD]
[/TR]
[TR]
[TD] Apple[/TD]
[/TR]
[TR]
[TD] Brand A[/TD]
[/TR]
[TR]
[TD] Brand B[/TD]
[/TR]
[TR]
[TD] Pear[/TD]
[/TR]
[TR]
[TD] Brand A[/TD]
[/TR]
[TR]
[TD] Brand B[/TD]
[/TR]
[TR]
[TD] France[/TD]
[/TR]
[TR]
[TD] Apple[/TD]
[/TR]
[TR]
[TD] Brand A[/TD]
[/TR]
[TR]
[TD] Brand B[/TD]
[/TR]
[TR]
[TD] Spain[/TD]
[/TR]
[TR]
[TD] Strawberry[/TD]
[/TR]
[TR]
[TD] Brand A [/TD]
[/TR]
</tbody>[/TABLE]
I need to display this indented data in three separate columns
[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Fruit[/TD]
[TD]Brand[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Apple[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Apple[/TD]
[TD]Brand B[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Pear[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Pear[/TD]
[TD]Brand B[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Apple[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Apple[/TD]
[TD]Brand B[/TD]
[/TR]
[TR]
[TD]Spain[/TD]
[TD]Strawberry[/TD]
[TD]Brand A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This will allow me to pivot if required. For example heading 1 is country and this is indented by 1 space, heading 2 is Produce and this is indented by 3 spaces, heading type is indented by 5 spaces.
Is there a formula I can use to keep the original indented column data but also view in a a tabular format.
Happy to send over an example Excel file.
Thanks,
Brett
