Making a summary of Column info

  • Thread starter Thread starter Big Ben
  • Start date Start date
B

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
 
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
 
Ben,

one way

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

HTH

norik
 
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
 
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
 
Back
Top