Find content in column and sort in specific order

A

Andy15

Board Regular
Joined
Apr 1, 2017
Messages
56
Hi Guys

I am looking for some help with finding some content within a column and re-arranging the order of the found rows to a specific order.

To make it a bit clearer please see below


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]score1[/TD]
[TD]score2[/TD]
[TD]score3[/TD]
[TD]score4[/TD]
[TD]score5[/TD]
[TD]score6[/TD]
[TD]score7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]red[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]blu[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]grn[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]ora[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD]bla[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[TD]number[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]tom[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ann[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]alan[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ellie[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]mary[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dave[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]lee[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The cells I need to reorder will always be in column 2 and will always be in the same order starting with "tom" and ending with "lee"

I would like to run a macro to find the relevant rows and all the relevant columns to the right and reorder them in the following order

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ann[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[TD]code2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]mary[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[TD]code5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ellie[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[TD]code4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]alan[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[TD]code3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]tom[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[TD]code1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]lee[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[TD]code7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dave[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[TD]code6[/TD]
[/TR]
</tbody>[/TABLE]

in the above examples the cells with code1, code2 are for example only, in reality they will be various numbers.

Hope this makes sense

Thanks for any help
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Code: 
Sub FT()
Dim rng As Range, LstNum%
Set rng = [B:B].Find("tom").Resize(7)
LstNum = Application.CustomListCount + 2
Application.AddCustomList Array("ann", "mary", "ellie", "alan", "tom", "lee", "dave")
rng.EntireRow.Sort key1:=rng(1), order1:=xlAscending, Header:=xlNo, OrderCustom:=LstNum
LstNum = LstNum - 1
Application.DeleteCustomList LstNum
End Sub
 
Last edited:
Upvote 0
If column A is like your sample, that is populated with values above tom and all blanks for the rows you want to sort & below, you could also try this in a copy of your workbook.
Code: 
Sub ReOrderNameRows()
  With Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(7).EntireRow
    .Columns(1).FormulaR1C1 = "=MATCH(RC[1],{""ann"",""mary"",""ellie"",""alan"",""tom"",""lee"",""dave""},0)"
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
    .Columns(1).ClearContents
  End With
End Sub
 
Upvote 0
Peter_SSs said:
If column A is like your sample, that is populated with values above tom and all blanks for the rows you want to sort & below,..
Click to expand...
With that same assumption and the added assumption that the extent of the columns is determined by the headings in row 1, here is another one to try.
Code: 
Sub ReOrderNameRows_v2()
  With Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(7, Cells(1, Columns.Count).End(xlToLeft).Column)
    .Value = Application.Index(.Value, Application.Transpose(Array(2, 5, 4, 3, 1, 7, 6)), Application.Transpose(Evaluate("row(1:" & .Columns.Count & ")")))
  End With
End Sub

Edit: Should also mention that this method does not preserve any formulas (they would be replaced by their results) & any formatting that is particular to cells in one of the rows will not "follow" the data from that row to its new location. So this suggestion is only about values.
 
Last edited:
Upvote 0
Hi Peter_SSs

The information to reorder is in column B, I have tried changing some of the 1's to 2's but I don't know enough to understand what I am doing.

Thaks
Andy
 
Upvote 0
Hi Peter_SSs

The codes in in column B, sorry if that was not clear. I do not understand enough to modify your code

Thanks
Andy
 
Upvote 0
If you had to modify my codes at all, it probably means that my original assumption from post 3 was incorrect as I had already allowed for the names to be in column B. I had assumed that column A was filled above the "tom" row and empty from the "tom" row down (like your posted sample).

If that is not the case, but the number of columns is set by row 1, you could also try this code, though I understand you already have a good working code from footoo. My 'Edit' notes from post 4 would still apply to this code.
Code: 
Sub ReOrderNameRows_v3()
  With Columns("B").Find("tom").Offset(, -1).Resize(7, Cells(1, Columns.Count).End(xlToLeft).Column)
    .Value = Application.Index(.Value, Application.Transpose(Array(2, 5, 4, 3, 1, 7, 6)), Application.Transpose(Evaluate("row(1:" & .Columns.Count & ")")))
  End With
End Sub
 
Last edited:
Upvote 0
Hi Footoo,

I am not sure why but on further testing, the code sorts as requested but when I try and save the workbook it gives an error Microsoft excel has stopped working.

I then have the options to check if there is solution or close the programme.

Thought it may help someone else that may try code.

Not sure if there is anything obvious why that would happen

Thanks
Andy
 
Upvote 0
Your assumptions were correct and my worksheet is as per sample, the code just seems to make the sheet flicker but nothing changes. I have tried your latest code and that is working brilliantly. Not sure what difference is but I am happy.

The code from Footoo seems to work until I try and save the workbook then it crashes.

Many thanks for your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Countif?
Replies
7
Views
566
cccbzg
C

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

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