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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
267
Peter_SSs
Peter_SSs
N
  • Solved
Command buttons not sorting - and sometimes jumping around on sort
2
Replies
10
Views
179
netxtown
N
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
427
Peter_SSs
Peter_SSs
S
  • Question Question
Sorting with VLOOKUP
Replies
1
Views
80
KillerOfGiants
K
C
  • Solved
Newbie Alert: Column-sorting question
Replies
4
Views
69
JamesCanale
JamesCanale

Forum statistics

Threads
1,224,750
Messages
6,180,740
Members
452,996
Latest member
nelsonsix66

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