Search code examples
excel-formularemoveallblank-line

MS EXCEL: Remove Empty (Rows) Lines Across Multiple Columns

With the use of a MS Excel Formula; I would like an array formula that will remove blanks rows across multiple columns.

Unfortunately and unlike the use of other similar formulae (i.e., =INDEX($B$3:$B$10,SMALL(IF(ISBLANK($B$3:$B$10),"",ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1),ROW(A1))) and alike these formulas only allow the removal of blank lines from a single column. Whereas, I am attempting to achieve the same results but the removal of blank lines from columns across multiple rows.

EXAMPLE:

(A2) John Doe | (B2) New York | (C2) NY

(A3) BLANK | (B3) BLANK | (C3) BLANK

(A4) Jane Doe | (B4) Baltimore | (C4) MD

(A5) BLANK | (B5) BLANK | (C5) BLANK

(A6) Mary Jones | (B6) San Francisco | (C6) CA

(A7) BLANK | (B7) BLANK | (C7) BLANK

(A8) BLANK | (B8) BLANK | (C8) BLANK

(A9) William Jones | (B9) BLANK | (C9) IL

enter image description here

RESULTS:

(A2) John Doe | (B2) New York | (C2) NY

(A3) Jane Doe | (B3) Baltimore | (C3) MD

(A4) Mary Jones | (B4) San Francisco | (C4) CA

(A5) William Jones | (B5) BLANK | (C6) IL

enter image description here

Solution

  • This function should work:

    {=IFERROR(INDEX($A$2:$C$10,MATCH(1,(COUNTIF($E$1:$E1,$A$2:$A$10)=0)*($A$2:$A$10>""),0),COLUMN(A1)),"")}

    Note that this formula only works, if you don't have duplicates in column A. Also blank cells (like B5 in your example) will be filled with a 0. You can remove those by using a IF formula:

    {=IFERROR(IF(INDEX($A$2:$C$10,MATCH(1,(COUNTIF($E$1:$E1,$A$2:$A$10)=0)*($A$2:$A$10>""),0),COLUMN(A1))>0,INDEX($A$2:$C$10,MATCH(1,(COUNTIF($E$1:$E1,$A$2:$A$10)=0)*($A$2:$A$10>""),0),COLUMN(A1)),""),"")}

    Copy into E2 and drag down and then right. Don't forget to enter with CTRL + SHIFT + ENTER