Sorting data retrieved by formulas?

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
176
Hi,

I have a table with data generated by formulas. When using the sort function in the filter I get lots of blanks between all values. I can not uncheck blanks in the filter since it will remove other information on the same row that is not related to the table data.

Any tips?
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,610
Office Version
365, 2016
Platform
Windows
What is your formula? You could change it to return 0 or not found.
 

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
176
What is your formula? You could change it to return 0 or not found.
=IF(INDEX($C$3:$C$300;MATCH(D3;$B$3:$B$300;0))="NO";D3;"") this formula retrieves the values in column E. Those values, I want to sort in column F. Please see example below. I can do that using the filter but the issue is there is information on the same row as the blanks moving around then.

Column E
(blank)
(blank)
89043
(blank)
75678
(blank)

Column F
89043
75678
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Not sure to understand your example ..

Are you looking for a formula to sort Column E ..

or

Are you looking for a formula to Delete all the Blanks ...???
 

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
176
Hello,

Not sure to understand your example ..

Are you looking for a formula to sort Column E ..

or

Are you looking for a formula to Delete all the Blanks ...???
Hi James,

A formula to sort the cells containing values and putting the blanks in the bottom. Without messing around information that might appear on the same row as the blank row.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
well sorting data ...

will not produce :

Column F
89043
75678

unless you need to produce a descending order...
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Say you create a Named Range for the data your Column E ( e.g. rng )

in cell F2, you can have the following Array formula:

Code:
=IF(ROWS($2:2)<=COUNTA(rng),INDEX(rng,SMALL(IF(rng<>"",ROW(rng)-MIN(ROW(rng))+1),ROWS($2:2))),"")
and copy down ...

Hope this will help
 

Watch MrExcel Video

Forum statistics

Threads
1,096,063
Messages
5,448,180
Members
405,488
Latest member
sunbeam906

This Week's Hot Topics

Top