Return Unique Consecutive Duplicate Values across Single Row

Definitely look at Domenic's response.
Where I misinterpreted your criteria, built a formula that listed
same-column multiples, then fiddled with it to list consecutive dupes....he
paid attention. His formula does what you want without all the side trips.

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

XL2002, WinXP


"Sam via OfficeKB.com" wrote:

Hi Ron,

Thank you very much for persevering.

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

A11:
=LARGE(INDEX((FREQUENCY((($A$2:$H$70)*($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

This is Great!

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

Yes,

Dare I ask?

Can the Formula actually list them in ascending order?

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

XL2002, WinXP

Cheers,
Sam

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

Hi Ron,

Thank you for all your help.

Will see Domenic's post, thanks.

Cheers,
Sam

Ron Coderre wrote:
Definitely look at Domenic's response.
Where I misinterpreted your criteria, built a formula that listed
same-column multiples, then fiddled with it to list consecutive dupes....he
paid attention. His formula does what you want without all the side trips.

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

XL2002, WinXP

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

Hi Domenic,

Thank you very much for your assistance. Does the job great. Brilliant!

Cheers,
Sam

Domenic wrote:
Assuming that A2:H8 contains the data, try the following...

J2:

=SUM(IF(FREQUENCY(IF(A2:H7=A3:H8,A2:H7),IF(A2:H7= A3:H8,A2:H7)),1))

...confirmed with CONTROL+SHIFT+ENTER

K2:

leave empty

L2, copied across:

=IF(COLUMNS($L$2:L2)=$J$2,MIN(IF(ISNA(MATCH($A$2 :$H$7,$K$2:K2,0)),IF($A$
2:$H$7=$A$3:$H$8,$A$2:$H$7))),"")

...confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

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