0

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
1

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

enter image description here

enter image description here

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(
              LEN(TRIM(INDIRECT(ADDRESS((1+H1),1)&":$A$100")))
             *LEN(TRIM(INDIRECT(ADDRESS((1+H1),2)&":$B$100")))
           )
         ,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).

2
  • 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.

enter image description here

enter image description here

enter image description here

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)

enter image description here

Select Load & Close. Done.

"Get & Transform > From Table" function is not available in Excel 2010

  • Go to: The Data tab > Sort & Filter > Advanced.

enter image description here

Note the Criteria, how it's typed in.

enter image description here

  • 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
  • 1
    @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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.