Excel Sorting

E

Excel1991

Board Regular
Joined
Aug 1, 2018
Messages
58
Hello All,
I am trying to sort some source data that comes in a formatas follows:
<tbody> [TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] John [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] Mark [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 2 [/TD]
[TD="width: 160, bgcolor: transparent"] 2 [/TD]
[TD="width: 160, bgcolor: transparent"] Amy [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
</tbody>

Basically it shows the name in one row, and then the correspondingvalues are beneath it, then the next name with the values for the person beneaththe name and so on. In order to sort the information, I create a third row andhave to manually enter the name next to the values like so.
<tbody> [TD="width: 160, bgcolor: transparent"] Name column [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] John [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] John [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] John [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] John [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] 3 [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] Mark [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] Mark [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] Mark [/TD]
[TD="width: 160, bgcolor: transparent"] 2 [/TD]
[TD="width: 160, bgcolor: transparent"] 2 [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] Amy [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] Amy [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] 1 [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
[TD="width: 160, bgcolor: transparent"] [/TD]
</tbody>

After I am done entering them this way I can just sort by thename column. I am just wondering if there may be an easier way to sort theoriginal source data I get, rather than having to manually enter the name like Iam doing
thank you
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
If you insert a column as you are already, then enter the formula into Cell A2 & copy down

=IF(C2="","",IF(ISTEXT(B1),B1,A1))
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]MARK
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FOMULA?
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JOHN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AMY
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MARK
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

I am having trouble following the formula. How would it correspond if my table is as follows
 
Upvote 0
If your data starts in cell B1, enter the formula in A2 and copy down.

Code: 
[TABLE="width: 490"]
<tbody>[TR]
[TD="class: xl64, width: 87, align: center"][/TD]
[TD="class: xl64, width: 229, align: center"]A[/TD]
[TD="class: xl64, width: 87, align: center"]B[/TD]
[TD="class: xl64, width: 87, align: center"]C[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]MARK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65, bgcolor: transparent"][COLOR=#333333]=IF(C2="","",IF(ISTEXT(B1),B1,A1))[/COLOR][/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]MARK[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]4[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl63"]JOHN[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]JOHN[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]6[/TD]
[TD="class: xl65, bgcolor: transparent"]JOHN[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]7[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl63"]AMY[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]8[/TD]
[TD="class: xl65, bgcolor: transparent"]AMY[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]9[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl63"]MARK[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]10[/TD]
[TD="class: xl65"]MARK[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]11[/TD]
[TD="class: xl65"]MARK[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]12[/TD]
[TD="class: xl65"]MARK[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Here is how I did it. I used Power Query/Get and Transform

Here is the Mcode
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Name", each if Value.Is([Column1],type text) then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Column1", "Column2"})
in
    #"Reordered Columns"
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
178
Peter_SSs
Peter_SSs
N
  • Solved
Command buttons not sorting - and sometimes jumping around on sort
2
Replies
10
Views
123
netxtown
N
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
260
Peter_SSs
Peter_SSs
B
  • Solved
OPTIMIZE my code
Replies
5
Views
301
BritsBlitz
B
G
  • Solved
Combine input box (multiple criteria)
Replies
4
Views
95
gomes123
G

Forum statistics

Threads
1,223,705
Messages
6,173,985
Members
452,540
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top