Removing blanks and sorting the list

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a column of data (DZ6 to DZ20) from cells with formulas which results in some with data and some with blank cells. The formula below is working for text and sorts the data ascending removing blank cells and places results in cells DZ300 onwards, but I have other column with numeric and blank cell results.
When I use this formula on the numeric cells it results in an error. I'm sure it's something to do with text versus numeric in the code, but can't work it out.
Can anyone advise what I need to change in the formula to remove blanks and sort the cells with data in descending order?
Any help is always appreciated.

=IFERROR(INDEX(DZ$6:DZ$20,MATCH(0,COUNTIF(DZ$6:DZ$20,"<"&DZ$6:DZ$20)-SUM(COUNTIF(DZ$6:DZ$20,DZ$300:DZ301)),0)),"")
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,323
Office Version
  1. 365
Platform
  1. Windows
As you are using 365, how about
Excel Formula:
=SORT(FILTER(DZ6:DZ20,DZ6:DZ20<>""))
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Thanks for the above.
Unfortunately it did not work, but it could have been me getting the cells incorrect.
However I did manage to search google and spotted the use of -
=sort(range,1,1)
and because I could use ascending instead of descending it made all the blanks go to the bottom.
Thanks for your help though.
Regards
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,323
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,367
Messages
5,547,499
Members
410,797
Latest member
mlfuson
Top