# 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:

``````=IF(ROW()>SUM(SIGN(LEN(TRIM(\$A\$1:\$A\$100))*LEN(TRIM(\$B\$1:\$B\$100)))),"",MATCH(1,SIGN(LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":\$A\$100")))*LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":\$B\$100")))),0)+H1)
``````

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) "Get & Transform > From Table" function is not available in Excel 2010

• Go to: The Data tab > Sort & Filter > Advanced. Note the Criteria, how it's typed in. • thx, it looks working but i'm looking for a CSE formula solution. "Get & Transform > From Table" function is not available in Excel 2010. – athos Apr 17 '17 at 23:38
• @athos Ah okay. I'm not sure if a CSE solution is available for what you are looking for. You'd have to express NOT EQUAL to NULL somewhere and sort a table to boot. However, "Get & Transform" is a new tool that IS NOT new at all; they just regrouped old items under new sections. If you want to try it out, I'll post an addendum on the bottom and you can try it out. I'll do that now. – ejbytes Apr 18 '17 at 0:07
• LOL @ the downvoter! I love your misery :P – ejbytes Apr 18 '17 at 0:41