advanced column filtering

[cal6404]

Hi, I have many columns in excel that I need have the rows within each sorted. Values in column B have been duplicated in column E at but they aren't in the same row. I need to match the rows from column B and E such that each row in B has the corresponding cell from column E below it. I would really appreciate the help.

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you post a small set of dummy sample data and the the expected results? My signature block below has a link with options for posting sample data that we can copy/paste to test with.

 

[cal6404]

Hi, sorry it's taken me a while to get back to you. Here is a small example of the before sorting and the result that I want after sorting. I need the Object ID value (say P00021) to be above the Unit (say P00021). Basically the rows with the corresponding Object ID and Unit values are scattered throughout my data and I need to have them shown as one above the other for the entire set of data.

[TABLE="width: 714"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Before sorting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]Object ID [/TD]
[TD]Description[/TD]
[TD]Unit[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AGITAT0086[/TD]
[TD]AGITATOR UNIT[/TD]
[TD]AG00091[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]AREA455[/TD]
[TD]Ammonium[/TD]
[TD]Area[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]P00021[/TD]
[TD]Pumping Unit 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]AG00091[/TD]
[TD]AGITATOR - TOP MOUNTED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]PUMP11001[/TD]
[TD]Centrifugal[/TD]
[TD]P00021[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]After sorting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]Object ID [/TD]
[TD]Description[/TD]
[TD]Unit[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]P00021[/TD]
[TD]Pumping Unit 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]PUMP11001[/TD]
[TD]Centrifugal[/TD]
[TD]P00021[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]AG00091[/TD]
[TD]AGITATOR - TOP MOUNTED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AGITAT0086[/TD]
[TD]AGITATOR UNIT[/TD]
[TD]AG00091[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]AREA455[/TD]
[TD]Ammonium[/TD]
[TD]Area[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Try this with a copy of your workbook. It uses columns B & D as your last post seemed to have that, rather than B & E.
If you need B & E and can't modify the code, post back.

Sub Special_Sort()
  Dim lr As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("A2:E" & lr)
    .Columns(5).Formula = Replace("=IF(ISNUMBER(MATCH(B2,D$2:D$#,0)),ROWS(E$2:E2),IFERROR(MATCH(D2,B$2:B$#,0)+0.5,ROWS(B$2:B$#)+1))", "#", lr)
    .Sort Key1:=.Columns(5), Order1:=xlAscending, Header:=xlNo
    .Columns(5).ClearContents
  End With
End Sub