Removing Blank Rows

I

inveni0

I have an excel workbook with three sheets:

_FIRST_SHEET_

This sheet has three columns. Column A is simply a set of random
numbers. Column B is empty and is designed so that the spreadsheet
user can place an x next to values in Column A that the user would like
to "mark". Column C has an "if" formula that basically says,
=if(B1="x", A1, "")

So now, any "A" cell that I select by placing an "x" in the "B" column
is copied into Column C.


_SECOND_SHEET_

I'd like Column A in this sheet to pull the values from Column C in the
previous sheet, but without skipping any lines. In other words, I want
the values from Sheet1:ColumnC, but collapsed so that they're all next
to each other--nice and neat.


Is this possible?
 
M

Max

One way, via non-array formulas ..

In Sheet1, slightly change the formula in col C
Put in C1: =IF(B1="x",ROW(),"")
Copy C1 down to last row of data in col A

Then in Sheet2, we could put in A1:
=IF(ISERROR(SMALL(Sheet1!C:C,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!C:C,ROW(A1)),Sheet1!C:C,0)))
Copy A1 down to cover the extent of col A in Sheet1

Col A will return the desired results from Sheet1's col A (i.e. numbers
marked with an "x" in Sheet1's col B), all neatly bunched at the top
 
H

Herbert Seidenberg

Another way, via Advanced Filter

ListA ListB ListC
A
B x B
C
D
E x E
F
G x G
H
I
J
K x K
L x L

Criteria
Test
#VALUE!

ListA
B
E
G
K
L

Select A1:A13 and name it Database.
This includes ListA and A thru L.
Use Insert > Name > Define
Again select A1:A13 and
Insert > Name > Create > Top Row
Select the 3 cells beginning with Criteria and
Insert > Name > Create > Top Row
In the cell that says #VALUE! enter this formula:
=OFFSET(ListA,,1)="x"
Data > Filter > Advanced Filter
Check: Copy to another location
The List and Criteria fields were filled automatically.
Copy To: enter a location on sheet2
 
I

inveni0

Thanks for the advice, guys. I can't try it now (I'm working on another
project) but I'll give it a shot tonight and post the results. I
appreciate the response!
 
Top