Tuesday, February 5, 2008

Excel tip: remove gaps in lists

Here's a formula to remove gaps in a range so that the list is contiguous:

{=IF(ROW()-ROW(W$53:W$69)+1>ROWS(A$53:A$69)-
COUNTBLANK(A$53:A$69),"",INDIRECT(ADDRESS(SMALL(
(IF(V$53:V$69<>0,ROW(A$53:A$69),ROW()+ROWS(A$53:A$69))),
ROW()-ROW(W$53:W$69)+1),COLUMN(A$53:A$69),4)))}

W$53:W$69 is the destination range where the gapless list items will be placed. This formula would be copied to each cell within this defined range. Notice it is a collection (ie. hit ctrl-shift-return when accepting the cell formula).

A$53:A$69 is the source range of values you want to copy. In this example, I want to copy the data in this column if the test of column V on the same row is true. The value in column A of the row where V is true is copied to the same row in column W, or a zero value is put in column W of the same row if the test is false.

V$53:V$69 is the test range. If this test is true, then it's a cell that has value.

If you want to use a different source range to determine whether to include a row or not, change the bolded range (IF(V$53:V$69)<>0) to the condition you want to check. It has to have the same number of cells as the source and destination.

Notice also the absolute rows. If your data is in a row with multiple columns, set the column to absolute. This way, you can copy the formula to every cell within the range easily, since the same formula is used in each cell within the destination range.

No comments: