Making a summary of Column info

[Big Ben]

Hello,

I have a column that has info dispersed throughout 400 rows within tha
column. For ex. in Column A I have values in rows 3, 10, 18, 200
249...etc. I would like to put this data onto another sheet, however
I would like to find a formula that might remove any spaces between th
rows so that on the new sheet it will list the data that was in row
into row 1, info in row 10 into row 2, etc.

Thanks for your help in advance

Be

 

[Frank Kabel]

Hi
if your data is in column A and you want to put the cleaned list in
column B put the following array formula in cell B1 (enter with
CTRK+SHIFT+ENTER)
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW($A$1:$A$10
00),1001),ROW()))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<>"",ROW(
$A$1:$A$1000),1001),ROW())))
copy this formula down

 

[norika]

Ben,

one way

Select Column A > edit > goto > special > blanks
edit > delete > entire rows

HTH

norik

 

[Big Ben]

Hi Guys,

Thanks a lot for your reply. Frank, I tried your method out and i
works for numbers that are manually inputted, but the column that
want to summarize has a formula in it...for ex..data in column A i
calculated by an if formula and returns values in some cells in th
column. Is there anyway to modify the formula that would account fo
this.

Norika, your method appears to have the same problem as mentione
above, but again, works with numbers manually entered. I believe th
formula is causing it to appear as a non blank cell.



Be

 

[Frank Kabel]

Hi Ben
it should work also for formulas. Do your IF functions look like
=IF(condition,formula," ")
so that you return a SPACE (" ") in case the condition is not met. If
yes change this to
=IF(condition,formula,"")

you may post your IF formula which causes the error