Excel Sorting

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:


John

1
1
3
3
3
3
Mark

1
1
2
2
Amy

1
1


<tbody> </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.
Name column



John

John
1
1
John
3
3
John
3
3

Mark

Mark
1
1
Mark
2
2

Amy

Amy
1
1



<tbody> </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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
MARK
FOMULA?
1
1
2
2
JOHN
1
1
3
3
AMY
4
4
MARK
5
5
3
3
3
3

<tbody>
</tbody>

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

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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