It's picking up the 100 in A7.

Biff

"Ron Coderre" wrote in message
...
Done?....not quite. The previous formula calc'd consecutive blanks as
dupes.

This fixes that:
A11:
=LARGE(INDEX((FREQUENCY((($A$2:$H$7<0)*($A$2:$H$7 =$A$3:$H$8)*($A$2:$H$7+COLUMN($A$2:$H$7)*1000)),($ A$2:$H$7+COLUMN($A$2:$H$7)*1000))=1)*ROW($A$1:IND EX($A:$A,ROWS($A$2:$H$7)*COLUMNS($A$2:$H$7)+1)),0) ,COLUMNS($A$11:A11))

How're we doing?

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Well, the formulas didn't get any prettier, but....I *think* this
works.....

A11:
=LARGE(INDEX((FREQUENCY((($A$2:$H$8=$A$3:$H$9)*($A $2:$H$8+COLUMN($A$2:$H$8)*1000)),($A$2:$H$8+COLUMN ($A$2:$H$8)*1000))=1)*ROW($A$1:INDEX($A:$A,ROWS($ A$2:$H$8)*COLUMNS($A$2:$H$8)+1)),0),COLUMNS($A$11: A11))

A12: =IF(A11,INDEX($A$2:$H$8,CEILING(A11/8,1),MOD(A11-1,8)+1),"")

Are we done yet?

***********
Regards,
Ron

XL2002, WinXP


"Sam via OfficeKB.com" wrote:

Hi Ron,

Thanks very much for reply and further input.

Ron Coderre wrote:
The issue must be data related. Are there Blanks? Text?
No Blanks, text only in Row "1" .

Those are the only exceptions that throw errors in my testing.

With the data in A2:H11, this formula is durable against blanks, but
not text

No text, although I did have a problem copying back my original Sample
Data
using Data Text to Columns which created the #N/A errors but sorted
now.

However, regarding my Sample Data and the Expected Results, I've
noticed that
your Formula picks out Numeric Value 102 as a consecutive duplicate
value in
the SAME column. It is a duplicate in the same column but NOT a
consecutive
duplicate as described in my original post. It should not be included
in the
results.

A11:
=LARGE(INDEX((FREQUENCY(($A$2:$H$8+COLUMN($A$2:$H $8)*1000),($A$2:$H$8+COLUMN($A$2:$H$8)*1000))1)*R OW($A$1:INDEX($A:$A,ROWS($A$2:$H$8)*COLUMNS($A$2:$ H$8)+1)),0),COLUMNS($A$11:A11))

Does that help?
or.... do you see anything else that may be an issue?

Numeric Value 102 is NOT a consecutive duplicate as described in my
original
post. However, the Formula does incorrectly return Numeric Value 102. I
think
this could be part of the problem and solution.

***********
Regards,
Ron

XL2002, WinXP

Cheers,
Sam

--
Message posted via http://www.officekb.com