Array formula

From: Ken McLennan (kenrmcl_at_dnet.net.au)
Date: 03/12/04 

Date: Sat, 13 Mar 2004 09:46:20 +1000

G'day there People,

        I've found one of Chip Pearson's formulae on his website, and
shamelessly borrowed it for my own little project. However, I don't seem
to be able to get it to work as it should (or as I think it should).

Here's the formula:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

It's on the page "Eliminating Blank Cells From Lists" and the example
given is thus:

    BlanksRange | NoBlanksRange
1 Able | Able
2 Baker | Baker
3 | Cathy
4 Cathy | Davis
5 Davis | Edward
6 |
7 Edward |

I've been able to get it to work on a single column range as per the
example. However, when I've tried to get it to work on a list of 3
columns (BlanksRange is 3 cols wide and NoBlanksRange is the same) I get
the first record duplicated down each row of the target range.

         From | To
   No Name Role | No | Name | Role
1 1234 | SMIFF | Manager | 1234 | SMIFF | Manager
2 4321 | JONES | S-visor | 1234 | SMIFF | Manager
3 9999 | LONG | Worker | 1234 | SMIFF | Manager

I've not yet gotten to the part where it removes blanks, I can't get it
to work on a full list. I tried copy|paste from the original work***
to all cells, and that didn't work. I tried copying to the top row and
then extending the range down as per instructions in the Excel 2003
Bible, but that didn't work. I've copied it to the top left cell &
extended both vertically & horizontally. No matter what I try, I can't
seem to get it to work as I think it should.

It successfully deals with the first row as a single unit, so therefore
I know that it can deal with more than one column. I just can't get it
to work with several rows while doing so.

I've currently got it working with 6 separate ranges (fromNo, toNo,
fromName, etc). Surely there's a more elegant way of doing it, or is it
a single column procedure?

See ya
Ken

Loading