excel formula needed

[cent]

I need a formula to
1) look in colunm a, gather any numbers present in that colunm, even though
most wil be empty
2) then i want them to take that list, get rid of the empty cells and put
that data either into a new table or on another sheet.
thanks.

 

[ryguy7272]

Sounds like a sort would work. If you want a function, try this
=IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL(IF(A1:A20<>"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter, not just enter...

HTH,
Ryan---

 

[Ashish Mathur]

Hi,

Select the range and press Ctrl+G > Special > Constants. This will select
all the non blank cells. If the non blank cells are the result of formulas,
then select formulas. Now copy the selected values and paste it in a new
range

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[JH]

You can also use the autofilter to filter the nonblank cells, copy and paste
to a new sheet or new range.
No formula needed.