OK....Here's the latest in a series of final formulas : \

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)+($A$2:$H$7<$A$3: $H$8)*9999)=1)*ROW($A$1:INDEX($A:$A,ROWS($A$2:$H$ 7)*COLUMNS($A$2:$H$7)+1)),0),COLUMNS($A$11:A11))

Copied across yields these results:
420 170 280 101 430 107

In ascending order that would be:
101 107 170 280 420 430

Dare I ask?

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

XL2002, WinXP


"Sam via OfficeKB.com" wrote:

Hi Ron,

Using the Sample Data the results do not tie up with the Expected Results -
Now Numeric Value 100 is being listed.

Expected Results: Unique Consecutive Duplicate Returned across Single Row in
ascending order.
101 107 170 280 420 430

Column 1 = 101
Column 3 = 107, 170
Column 6 = 280, 430
Column 7 = 420


Ron Coderre wrote:
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:IN DEX($A:$A,ROWS($A$2:$H$7)*COLUMNS($A$2:$H$7)+1)),0 ),COLUMNS($A$11:A11))

How're we doing?

Not quite there, yet.

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

XL2002, WinXP

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

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1