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

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
67
Office Version
365
Platform
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,669
Office Version
2019
Platform
Windows
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)),"")
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
67
Office Version
365
Platform
Windows
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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,669
Office Version
2019
Platform
Windows
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)),"")
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
67
Office Version
365
Platform
Windows
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.
CheersSort Order.JPG
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,669
Office Version
2019
Platform
Windows
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)),""))
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
67
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,030
Messages
5,472,094
Members
406,801
Latest member
Kiran2229Kiran

This Week's Hot Topics

Top