Reduce a sparsely populated range in Excel

Is there a way of using Excel's built in functions to remove all blank cells in a range?

A quick a dirty way would be to sort the range, so that the empty cells ends up last, but if I don't want to change the order of the element that is not a good way of doing it.

I know I can do this in VBA, and already has, but I'm more interested if this functionality is avaliable from within Excel.

Example:

 A  C D   F  E

should end up with

 A C D F E

Replay

Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go to Edit>Delete.

Edit:

No edit menu in Excel 2007? I must be showing my age. Thanks, Jason Z!

Put your sparsely populated range in Column C, stating at cell C2.

In B2:Bn put the following formula:

=IF(ISBLANK(C2),"",MAX($B$1:B1)+1)

This will number the populated cells, 1 to x (where x is the number of populated cells)

In A2:An put the following formula:

=IF(ISERROR(MATCH(ROW()-1,B:B,0)),"",VLOOKUP(ROW()-1,B:C,2,0))

This performs a lookup on the rownumber of Cell An-1 and brings the value across.

As the number of populated cells (x) is less than the number of cells in the range (n), you need to have the IF(ISERROR(MATCH(ROW()-1,B:B,0)),"" to avoid cells Ax+1:An being filled with "#NA"

The only way I have done this before is to insert another 'key' column (numbers 1 - n) alongside the sparse range. This allows you to reorder the data in its original sequence after you have sorted out the blanks.

context:

  • Excel 2003
  • know the filter mode

pre :

  1. insert a row at the top (maybe not necessary)
  2. select the range you want
  3. "put" a filter on it

now two ideas:

a) Non Destructive : in the column containing A B C D F E, tell excel to show the 'Non Empty' cells so they won't be displayed

b) Destructive : if you want to delete those lines definitively, choose 'Empty', the table will look empty. Select lines then delete them. Deactivate the filtering and you should end up with your data in order.

Hope that helps

While I agree with Richard, there is no Edit menu in Excel 2007. You need to be in the Home tab on the ribbon and click the Find & Select icon. Select Go To Special, check Blanks, and click OK. You can now delete the selected cells.

You can also use the data filter advanced filter and filter in place the unique items. You will be left with one blank cell however as that is a unique result for that cell value.

  1. Make another column and fill it with ordered numbers (1,2,3 ...)
  2. Then make one more column with formula:

    =IF(A1="";B1;"")

Where A1 is column from your example and B1 - added colunm with ordered numbers

Last. Sort data by column with formula

Highlight the range. Use Goto (F5) Special Constants. Edit Copy . Click in an unused area of the spreadsheet. Edit Paste. Now, if desired, copy the result back over the origin.

You have not mentioned what version of Excel you are using.

Apart from all the answers given here, you could try the 'Removing Duplicates Option' which will remove all the blanks and leave you with one blank cell. It will remove the row also which contain the blank.

Here is how you go about it:

Ribbon > DATA Tab > Remove Duplicates

NOTE: Use this with caution, as this will also delete all other non-blank but duplicate cells. I provided this response with respect to your example.

Category: microsoft excel Time: 2008-08-28 Views: 1

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.172 (s). 12 q(s)