Remove Blank Rows using formulas

[vaishaks89]

Hi there,

Good morning guys.

Today I am here with another query.

I have an excel sheet that has 2 columns in it. Column C holds customer names and Column D holds the revenue details.
Right now, the excel sheet has blank values for both columns (ie: no customer name and no revenue).

I want to remove those rows without using filters or without using macro. I want to use only formula to remove it.
Could u help me out in this? I am attaching the file in this post.

Thanks
Regards
Vaishak S

 

[Khalid NGO]

Hi and good day Vaishak,

Rows can not be deleted with formula only, you will need a VBA.

But you can sort your numbers (largest to smallest or smallest to largest) with formulas, and then lookup text values.

For example, use this {array formula} in any helper column, say column E5:
=IFERROR(LARGE(IF($A$5:$A$243<>"",$B$5:$B$243),ROW(A1)),0)
{array formula needs to be entered with Ctrl+Shift+Enter, not just enter}

Use this in D5 with just enter:
=IF(E5=0,"",INDEX($A$5:$A$243,MATCH(E5,$B$5:$B$243,0)))

Copy down both till row 243.

or see the attached.

Regards,

 

[bosco_yip]

Another option (without array formula),

1] D5, formula copy down :

=IFERROR(LOOKUP(1,0/(LARGE(B$5:B$243,ROWS($1:1))=B$5:B$243),A$5:A$243),"")

2] E5, formula copy down :

=IF(D5="","",VLOOKUP(D5,A$5:B$243,2,0))

Regards
Bosco

 

[chssundeep]

Hi Vaishak,

One more solution

In A4 cell type COMPANY NAME.... Now apply filter and select Sort A to Z now easily you can delete the blank cells

 

[Khalid NGO]

I want to remove those rows without using filters or without using macro. I want to use only formula to remove it.

Hi @chssundeep

OP needs formula solution only.

Regards,

 

[vaishaks89]

Hi Khalid,
Thanks a lot for the solution. It worked like a gem.
Thank you to all guys who tried it.

Regards
Vaishak S

 

[paramveer]

if we just remove blank rows only rather than sorting then how i do this

 

[bosco_yip]

if we just remove blank rows only rather than sorting then how i do this

Just remove blank cells only :

Highlight range of cells >> Ctrl+G >> click Special.. >> choose Blanks >> OK >> right click >> select Delete.. >> choose Entire row.. >> OK

Regards
Bosco

 

[paramveer]

sir, I don't want to delete blank rows
in your example file i need same result but don't change the order of item

 

[bosco_yip]

sir, I don't want to delete blank rows
in your example file i need same result but don't change the order of item

Please see the attached

Regards

 

[paramveer]

thanks sir thanks so much
please solve my one problem in question forum

 

[Deepak]

If formula is not the hype then advance filter would also a alternative.