# how to filter a range to a range using CSE formula?

I have a range A1:C100, cells in columns A and B might be blank. I'd like to use CSE formula, to filter A1:C100, only remain rows that A and B columns are not empty, and fill this into range D1:F100.

in SQL this would be straight forward, e.g.

select A, B, C from [A1:C100] where A!="" and B!=""

but how could i use CSE formula to write this in Excel?

• you can use Filter – yass Apr 17 '17 at 15:46
• @yass no. Filter only hide rows, not exclude rows. – athos Apr 17 '17 at 15:48
• @yass yeah. That's my intention. – athos Apr 17 '17 at 15:48
• @yass yes, i can manually copy, the whole question's purpose is to do things with excel formula to avoid any manual copy or paste or filter. – athos Apr 17 '17 at 16:02
• Use: Data Tab, choose "From Table" in "Get&Transform" section, "Remove Blanks" filter on Query. – ejbytes Apr 17 '17 at 17:36

Check out this example, with one spare column and three kinds array formulas.

Range A1:B100 is filled with random strings, with blank cells, range C1:C100 is just some numbers (could be strings as well).

Range H1:H100 is used to as a helper column, which actually filters out and accumulates the row numbers with non-empty cells in A and B.

H1: array formula to find the first row number with non-empty cells in A and B columns:

=MATCH(1,SIGN(LEN(TRIM(\$A\$1:\$A\$100))*LEN(TRIM(\$B\$1:\$B\$100))),0)

H2: another array formula to find all the rest row numbers with non-empty cells in A and B columns:

The formula is quite long, so just in case, here it is, formatted to show the structure:

IF(
ROW()>
SUM(
SIGN(
LEN(TRIM(\$A\$1:\$A\$100))*LEN(TRIM(\$B\$1:\$B\$100))
)
)
,""
,MATCH(1,
SIGN(
)
,0
)+H1
)

Type in the last kind of array formula in D1:

=IF(ROW()>SUM(SIGN(LEN(TRIM(\$A\$1:\$A\$100))*LEN(TRIM(\$B\$1:\$B\$100)))),"",INDEX(\$A\$1:\$C\$100,\$H1,COLUMN()-COLUMN(\$D\$1)+1))

copy the cell to E1, F1; select and copy range D1:F1; select range D2:F100 and paste copied cells.

The formulas could be much shorter at expence of more helper columns.

Obligatory note: to enter the array formula, press Ctrl+Shift+Enter instead of just Enter for scalar formula).

• Choose Data Tab
• Select From Table, in the "Get & Transform" section.
• Choose filters, "Remove Blanks", when prompted.
• or
• Advanced Filter: where ColA not equal to null AND ColB not equal to null for example.

You can also add an AND clause in your logical query (e.g. where ColA Not equal to null AND ColB Not equal to null)