Sort a 2 array of data

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135
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)
1600959736966.png





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



The target out put should be like this.
1600960120790.png


Thanks :)
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
In that case try
Excel Formula:
=SORT(FILTER(A2:Q44,B2:B44<>""),1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.

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

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135

ADVERTISEMENT

In that case try
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You said you were using 365.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
How about
In A70 filled down
Excel Formula:
=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
Excel Formula:
=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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,574
Members
414,390
Latest member
plimbu

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
Top