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.

Before sorting
Type	Object ID	Description	Unit
A	AGITAT0086	AGITATOR UNIT	AG00091
P	AREA455	Ammonium	Area
P	P00021	Pumping Unit 6
P	AG00091	AGITATOR - TOP MOUNTED
A	PUMP11001	Centrifugal	P00021
After sorting
Type	Object ID	Description	Unit
P	P00021	Pumping Unit 6
A	PUMP11001	Centrifugal	P00021
P	AG00091	AGITATOR - TOP MOUNTED
A	AGITAT0086	AGITATOR UNIT	AG00091
P	AREA455	Ammonium	Area

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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