Move Value between blanks and skip the blank cells

Copper Contributor

Hello,

I've column that's include a values and blanks so i just need to move only values without taking the blanks with the formula 

 

what i tried is : 

 

used =IF(Main!O5,Main!J5,"") so the formula find true in the Main sheet and then return the value of column  " J " 

 

what i've this : 

 

TRUE
TRUE
 
 
 
 
 
 
 
 
 
TRUE
TRUE
FALSE
TRUE
TRUE
TRUE
FALSE
FALSE

 

 

what i get as a result is : 

 

Mini,4x12
Armored 
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
HDPE 2 
Mini12
 
Mini, 8x12
MiniXt 12x12F
Mini-DQ(ZN)2Y, 18x12
 
 

 

so i don't need the result to be blanks and #VALUE! i need only getting the result of TRUE

 

6 Replies
Just curious if you need to take the filled cells then why not try pivot and then unselect blank?

@abdullahmuthanna 

If it is simply a question of filtering out blanks from a list then, using 365, the following would work

= FILTER(list, list<>"")

In legacy Excel it is distinctly more messy

= INDEX(list,SMALL(IF(LEN(list)>0,seq),seq))

committed with CSE.

 

@jitinm 

 

I want to do it automatically because i'm dealing with large data so i can't select and delete blanks every time i entered the data

What do you mean by " list " ? should i apply the index with the first one ? which is true or false ? or apply it with the values table ?
Guys the idea is i just want to return values that's include True ( because it will return me the value ) and i want to skip the blanks ( because it will return me #Value! )

@abdullahmuthanna 

'list' is a defined name that I have applied to whatever range of values from which you wish to remove the blanks.  If the quantities are meaningful to you, then it would be reasonable to apply a more specific name.

[Having described A1 type references as an abomination that should never entered any computational environment anywhere (thus placing me at odds with virtually every spreadsheet user) I am not the right person to consult on the mysteries of the semi-anchored, directly-referenced range.]