Sort a 2 array of data

[blackorchids2002]

Hi Masters,

I need your help again. I was trying to use an index formula to sort the 2 sets of data from a smallest value.
I cannot get it sorted how I want the data should look like in my excel.

=IFERROR(INDEX(A$1:A$44,SMALL(IF($B$1:$B$44<>"",ROW($2:$44)-ROW($2:$2)+1),ROW($A2))),"")

1ST SET OF DATA WITH REGULAR PRICE (same excel sheet)

2ND SET OF DATA FOR THE DISCOUNT (same same excel sheet

The target out put should be like this.

Thanks

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[blackorchids2002]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

MS 365

 

[Fluff]

In that case try

=SORT(FILTER(A2:Q44,B2:B44<>""),1)

 

[blackorchids2002]

In that case try

=SORT(FILTER(A2:Q44,B2:B44<>""),1)

Perferct.. Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

PS please don't forget to update your account details & then scroll down & click save.

 

[blackorchids2002]

In that case try

=SORT(FILTER(A2:Q44,B2:B44<>""),1)

Hi Fluff,

The sort formula is not working in MS Office Pro 2016.
This is my problem.

 

[Fluff]

You said you were using 365.

 

[blackorchids2002]

You said you were using 365.

on the remote desktop, the excel version is Office Pro 2016

 

[Fluff]

How about
In A70 filled down

=IFERROR(INDEX(A$2:A$44,AGGREGATE(15,6,(ROW($A$2:$A$44)-ROW($A$2)+1)/($A$2:$A$44=SMALL($A$2:$A$44,ROWS($A$70:$A70))),1)),"")

in B70 across & down

=IFERROR(INDEX(B$2:B$44,AGGREGATE(15,6,(ROW($A$2:$A$44)-ROW($A$2)+1)/($A$2:$A$44=SMALL($A$2:$A$44,ROWS($A$70:$A70))),COUNTIFS($A$70:$A70,$A70))),"")