Finding alternative formula for excel 2013 versus one used in 365 for making a list with empty cells start from the top

Peter Davison

Active Member
Joined
Jun 4, 2020
Messages
433
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have the formula (created in Excel 365) below which takes a list of populated and blank cells in a column (range named "DropDown_List" and sorts so all the entries are at the top and blanks at the bottom. Unfortunately the formula does not work for my colleague who has Excel 2013. (He gets the code at the beginning "xlfn" which I believe means the formula is only in Excel 365.

Can you advise if there is a formula that will do the same but can work in Excel 2013?

This is the formula created in Excel 365 -
=IFERROR(SORT(INDEX(DropDown_List, MATCH(0, IF(MAX((COUNTIF($AF$87:AF87, DropDown_List)=0)*((DropDown_List<>"")*(COUNTIF(DropDown_List, ">"&DropDown_List)+1)))=(IF((DropDown_List<>""), COUNTIF(DropDown_List, ">"&DropDown_List)+1, "")), 0, ""), 0)),1),"")

This is what shows in Excel 2013 - (Highlighted in red where it shows its not compatible with Excel 2013.
=IFERROR(_xlfn._xlws.SORT(INDEX(DropDown_List, MATCH(0, IF(MAX((COUNTIF($AF$87:AF87, DropDown_List)=0)*((DropDown_List<>"")*(COUNTIF(DropDown_List, ">"&DropDown_List)+1)))=(IF((DropDown_List<>""), COUNTIF(DropDown_List, ">"&DropDown_List)+1, "")), 0, ""), 0)),1),"")

Any help would be great.
Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
That's a bit of a messy formula to try and figure out without a data sample, see if this does what you need. Must be array confirmed with Ctrl Shift Enter.

=IFERROR(INDEX(DropDown_List,MATCH(IF(ROWS(AF$87:AF87)=1,1,0),COUNTIFS(DropDown_List,IF(ROWS(AF$87:AF87)=1,"<=","<")&DropDown_List,DropDown_List,"<>")-SUM(COUNTIFS(DropDown_List,"="&C$1:C1,DropDown_List,"<>")),0)),"")
 
Upvote 0
Hi thanks for the above.
Can you advise what the "C" column is in your formula?. As the formula is going into cell AG88 to AG436 should the C be AG?
Thanks
 
Upvote 0
Sorry, I was testing the formula with a smaller range, I thought I changed it to match your original formula before I posted it.

I had assumed from your formula that it was going into AF87, the formula below is correct for AG88

=IFERROR(INDEX(DropDown_List,MATCH(IF(ROWS(AG$88:AG88)=1,1,0),COUNTIFS(DropDown_List,IF(ROWS(AG$88:AG88)=1,"<=","<")&DropDown_List,DropDown_List,"<>")-SUM(COUNTIFS(DropDown_List,"="&AG$87:AG87,DropDown_List,"<>")),0)),"")
 
Upvote 0
Hi,
Thanks for getting back to me.
The result of the formula is just blank cells.
I've snipped a picture attached for you to see.
Column AE is looking at Cell AE86 and matching with Column D, if true returns relevant cell in Column B or leaves the cells in AE blank, so I was looking for code to make sure that column AE list wherever it is in the list is at the top in Column AG.
As you know I have the formula in my first communication working, but unfortunately only works in 365 so was looking to get a formula that could work in Excel 2013.
Hope the picture helps?

Thanks for all your help.
Cheers Sort Order.JPG
 
Upvote 0
Are there any other requirements for the formula? I can't see why it is not working from a screen capture, my best guess would be that the array is not confirmed correctly.

Trying to decipher your original formula, I thought that it was filtering out duplicate entries to only show each result once and sorting them in ascending order. To produce a list in the way that you were trying to in excel 2013 (assuming that I was following correctly) means comparing the list to the results already produced, which is why the formula in AG88 needs to look at AG87 (this part is dynamic so it changes as you fill down).

As it appears that duplicates are unlikely in column B, if you just want to compress the list without sorting the non blanks then you could do it with this in AE88 and filled down, no need for the second column. This will work in all versions of excel from 2010 onwards.

=IF($AE87="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$88:$B$436)/($D$88:$D$436=$AE$86),ROWS($AE$88:$AE88)),""))
 
Upvote 0
Thank you the last formula worked perfectly. You are right, there will never be a duplicate in Column B.
I really appreciate your time and perseverance with me as a beginner.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top