Sorting multi-level data in a table

Xcythe · Aug 19, 2018

Hey guys, hoping I can figure out if this is possible to do.

So basically I'm constructing a sort of project manager buy breaking down the steps. The below table is an example of what I am trying to achieve.

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Priority[/TD]
[TD]Project[/TD]
[TD][/TD]
[TD]Step #1[/TD]
[TD][/TD]
[TD]Step #2[/TD]
[TD][/TD]
[TD]Step #3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Education[/TD]
[TD]>>[/TD]
[TD]Reading[/TD]
[TD]>>[/TD]
[TD]Think and Grow Rich By Napoleon Hill[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]>>[/TD]
[TD]The Original Species by Charles Darwin[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]House Work[/TD]
[TD]>>[/TD]
[TD]Brace trellising[/TD]
[TD]>>[/TD]
[TD]Buy timber brace[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]>>[/TD]
[TD][/TD]
[TD]>>[/TD]
[TD]Cut and fix brace[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]>>[/TD]
[TD]New curtains[/TD]
[TD]>>[/TD]
[TD]Research curtains[/TD]
[TD]>>[/TD]
[TD]Research online[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]>>[/TD]
[TD]Vist shops that sell curtains[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]>>[/TD]
[TD]Buy new curtains[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Misc.[/TD]
[TD]>>[/TD]
[TD]Etc.[/TD]
[TD]>>[/TD]
[TD]Etc.[/TD]
[TD]>>[/TD]
[TD]Etc.[/TD]
[/TR]
</tbody>[/TABLE]

This provides me with the easiest method to see exactly what steps I need to take to complete a project. It also looks much better with formatting.

Now what I would like to do is be able to sort the data by the priority column but have anything with multiple steps in the correct order.

Is this possible to do?

Thank you in advance for your time.

lrobbo314 · Aug 19, 2018

If you add values like 1.1, 1.2, etc. in the blank cells in column A it will sort correctly. And if you don't want to see the 1.1, 1.2, etc. you can hide them with conditional formatting.

<table valign="top" border="1"><caption>LEGO HTML</caption><col width="54"><col width="122"><col width="140"><col width="95"><col width="154"><col width="95"><col width="279"><col width="95"><col width="223">
<tr><td></td><th style="background-color:rgb(166, 166, 166)">A</th><th style="background-color:rgb(166, 166, 166)">B</th><th style="background-color:rgb(166, 166, 166)">C</th><th style="background-color:rgb(166, 166, 166)">D</th><th style="background-color:rgb(166, 166, 166)">E</th><th style="background-color:rgb(166, 166, 166)">F</th><th style="background-color:rgb(166, 166, 166)">G</th><th style="background-color:rgb(166, 166, 166)">H</th></tr>
<tr><th style="background-color:rgb(166, 166, 166)">1</th><td style="background-color:rgb(250, 250, 250)">Priority</td><td style="background-color:rgb(250, 250, 250)">Project</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">Step #1</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">Step #2</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">Step #3</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">2</th><td style="background-color:rgb(250, 250, 250)">1</td><td style="background-color:rgb(250, 250, 250)">House Work</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Brace trellising</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Buy timber brace</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">3</th><td style="background-color:rgb(250, 250, 250)">1.1</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Cut and fix brace</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">4</th><td style="background-color:rgb(250, 250, 250)">1.2</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">New curtains</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Research curtains</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Research online</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">5</th><td style="background-color:rgb(250, 250, 250)">1.3</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Vist shops that sell curtains</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">6</th><td style="background-color:rgb(250, 250, 250)">1.4</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Buy new curtains</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">7</th><td style="background-color:rgb(250, 250, 250)">1.5</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">8</th><td style="background-color:rgb(250, 250, 250)">2</td><td style="background-color:rgb(250, 250, 250)">Education</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Reading</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Think and Grow Rich By Napoleon Hill</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">9</th><td style="background-color:rgb(250, 250, 250)">2.1</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">The Original Species by Charles Darwin</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">10</th><td style="background-color:rgb(250, 250, 250)">2.2</td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td><td style="background-color:rgb(250, 250, 250)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">11</th><td style="background-color:rgb(250, 250, 250)">3</td><td style="background-color:rgb(250, 250, 250)">Misc.</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Etc.</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Etc.</td><td style="background-color:rgb(250, 250, 250)">>></td><td style="background-color:rgb(250, 250, 250)">Etc.</td></tr></table>

Xcythe · Aug 19, 2018

It works, thanks a lot!

I do have another question; is it possible to have it automatically fill cells in 0.01 increments until it reaches a whole number that I have typed in manually? I know it's easy enough with auto fill, but I would like to make this workbook as dynamic as possible to prevent extra work once it's ready to go.

lrobbo314 · Aug 20, 2018

This will fill the decimal points and sort the table automatically.

Code:

Sub dSort()
Dim LR As Long: LR = Range("B" & Rows.Count).End(xlUp).Row
Dim R As Range: Set R = Range("A2:A" & LR).SpecialCells(xlCellTypeBlanks)
Dim Tbl As Range: Set Tbl = Range("A1:H" & LR)


R.FormulaR1C1 = "=R[-1]C+.01"
Tbl.Value = Tbl.Value
Tbl.Sort key1:=Tbl.Cells(1, 1), order1:=xlAscending, Header:=xlYes


End Sub

Xcythe · Aug 21, 2018

That's really cool, I can understand it mostly.

How would I get it to read the actual number under priority then attach 0.1 to it so I could easily reorder them as priorities change? Also, what kind of fail-safe can be used in case I accidentally add the same priority number to more than one project? (to prevent jumbling up).

Thank you.

Sorting multi-level data in a table

Xcythe

New Member

Excel Facts

lrobbo314

Well-known Member

Xcythe

New Member

lrobbo314

Well-known Member

Xcythe

New Member

Forum statistics

Share this page

Sorting multi-level data in a table

Xcythe

New Member

Excel Facts

lrobbo314

Well-known Member

Xcythe

New Member

lrobbo314

Well-known Member

Xcythe

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock