Assistance in Sorting excel data

[soumen21]

Dear Experts,

I have some data in this format

I want to sort in the below format.

Can anyone help. I'm using Microsoft 365.

Best Regards

 

[hagia_sofia]

Would this work for your data:

=LET(
array1,D1:D6,
array2,B1:D6,
array3,A1:B6,
non,--(NOT(ISBLANK(array1))),
a,IF(non=1,array2,""),
b,DROP(REDUCE("",SEQUENCE(3),LAMBDA(c,d,HSTACK(c,SCAN("",CHOOSECOLS(a,d),LAMBDA(a,b,IF(b<>"",b,a)))))),,1),
c,FILTER(b,non=0),
d,FILTER(array3,non=0),
CHOOSECOLS(HSTACK(c,d),1,4,5,2,3))

 

[RobP]

@hagia_sofia's is pretty slick .. I went for something a bit more understandable (but likely slower as a result..)

Assuming your data starts in A1 - First loop is to process the data, second loop is to delete the now unwanted rows (because I couldn't find a way to do it on the first pass )

Just to add, it should also work where you have rows of just one, or maybe three APPROVALS as I'm pretty sure those will come up.

Sub sort_data()

LR = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

Columns("A:A").EntireColumn.Insert

For x = 1 To LR
     
    If Range("D" & x) = "APPROVED" Then
        Range("A" & x) = Range("A" & x - 1)
        Range("D" & x) = Range("D" & x - 1)
        Range("E" & x) = Range("E" & x - 1)
    Else
        Range(Cells(x, 1), Cells(x + 1, 1)) = Range("C" & x)
              
    End If

Next x

For x = LR To 1 Step -1

    If IsNumeric(Range("B" & x)) Then Range("A" & x).EntireRow.Delete

Next x

End Sub

Book1
	A	B	C	D	E
1	42G4000-0002	TEXAS INSTRUMENTS TEXAS INSTRUMENTS	LM358DR	358 DUAL OP AMP, SO PKG	IC-ANALOG-OPAMP
2	42G4000-0002	TEXAS INSTRUMENTS TEXAS INSTRUMENTS	LM358DRE4	358 DUAL OP AMP, SO PKG	IC-ANALOG-OPAMP
3	42G4000-0162	TEXAS INSTRUMENTS TEXAS INSTRUMENTS	SN74AHCT245DBR	74AHCT245 OCTAL BUS XCVR SSOP-20	IC-DIGITAL-LOGIC
4	42G4000-0162	TEXAS INSTRUMENTS TEXAS INSTRUMENTS	SN74AHCT245DBRG4	74AHCT245 OCTAL BUS XCVR SSOP-20	IC-DIGITAL-LOGIC
Sheet1

 

[alansidman]

An alternative with Power Query aka Get and Transform Data which is on the Data Tab of the ribbon

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1]=100 or [Column1]=101 then [Column2] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Column1]=100 or [Column1]=101 then [Column3] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Column4", "Custom", "Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] = "TEXAS INSTRUMENTS TEXAS INSTRUMENTS")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "Column1", "Column2", "Column3", "Column4"})
in
    #"Reordered Columns"

 

[RobP]

Thanks a lot,

glad we were able to help.

Appreciate the feedback.

Rob