Baffling formula problem

[Ken Schmidt]

I have a really strange problem that just has me baffled. I have a
spreadsheet set up that pulls out unique items from a list. It works really
well. Here is the setup:

Cell F4:
=OFFSET($D$3,MIN(IF(COUNTIF($F$2:F3,D3:$D$201)=0,(ROW(D3:$D$201)-ROW($D$3)))
),0)
This formula is then copied down to F30.
Cells D4 to D30 have various names, some of which are duplicates.
Cell D3 has an apostrophe (cell appears blank).
Let's say there are 12 unique names in the list. Cells F4 to F15 then lists
all of them, once each.
Cell F16 has a zero.
Cells F16 to F30 are blank (this is the reason for the apostrophe in cell
D3).
So it works great. I get my unique list, a zero to signify the end of the
list, and blanks after that.

Here's the problem. If I do any editing of the formulas in the F-column,
the process no longer works correctly. This prevents me from extending the
range of my D-column. If I just copy the existing formula further down, it
still works. One thing that seems to do the trick is making the formulas
array formulas. However, this GREATLY increases the calculation time. And
the formulas that work before editing are NOT array formulas. Any help
would be appreciated.

Ken

 

[Ken Schmidt]

I found the source of the formula, a post in this group by Leo Heuser on
Mar. 10, 2000. It WAS supposed to be entered as an array formula. I wonder
why it worked the other way.

Ken

 

[Max]

If you can live with an additional helper col,
perhaps this non-array set-up might be one acceptable option ..

Assume your items are listed in D5500
(range may also contain blank cells in-between)

Put in E5:
=IF(D5="","",IF(COUNTIF($D$55,D5)>1,"",ROWS($A$1:A1)))

Put in F5:
=IF(ISERROR(MATCH(SMALL($E$5:$E$500,ROWS($A$1:A1)),$E$5:$E$500,0)),"",INDEX(
$D$5:$D$500,MATCH(SMALL($E$5:$E$500,ROWS($A$1:A1)),$E$5:$E$500,0)))

Select E5:F5, fill down to F500

(Hide away col D, if desired)

Col F will return the unique list of items,
with the first blank ("") cell in the column
signifying the end of the uniques list

Adapt/extend to suit

---

Cell F4:
=OFFSET($D$3,MIN(IF(COUNTIF($F$2:F3,D3:$D$201)=0,(ROW(D3:$D$201)-ROW($D$3)))
),0)

Was puzzled with your post that the formula above doesn't
need to be array-entered to work the way you described?

Found I had to array-enter it .. and that any blank cells
in-between in the source range in D4201
would also throw the results in col F a little off

 

[Max]

I found the source of the formula,
a post in this group by Leo Heuser on Mar. 10, 2000.
It WAS supposed to be entered as an array formula.

I wonder why it worked the other way.

Really not sure how you got it to function
in the manner described in your original post
w/o array-entering <g>

 

[Ken Schmidt]

Thanks very much for the reply Max, and the alternate formula. I will give
it a try.

Ken

 

[Max]

You're welcome, Ken !