Hi All,

I have a Named Range called "Data" that spans 8 columns and 7 rows. A Numeric
Value will appear only once in a row. Each row's Numeric Values are in
ascending order. A unique consecutive duplicate MUST be in the SAME column.

I would like to Return Unique Consecutive Duplicates across a Single Row in
ascending order.

I have a similar Data Layout and Scenario to that listed below. The main
difference is the numeric values in the cells are NOT constants - they are
formulas that pull the resultant numeric vales from another worksheet in the
same workbook. For example, in the Sample Data Layout where it displays
numeric values such as:

101 102 107 110 145 370 490 501

they are actually the end result of a Formula in those individual cells.

Should I be able to use Domenic's Formulae below to get the correct results
from my NEW Sample Data cells that contain Formulas rather than numeric
constants?

Please advise.

Thanks
Sam

Domenic provided me with this great solution for my original scenario which
contained only numeric constants:

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!


Original Scenario:
I have a Named Range called "Data" that spans 8 columns and 7 rows. A Numeric
Value will appear only once in a row. Each row's Numeric Values are in
ascending order. A unique consecutive duplicate MUST be in the SAME column.

I would like to Return across a single row unique consecutive duplicates
(single instance
of a consecutive duplicate value in the SAME column) in ascending order.

Sample Data Layout:

101 102 107 110 145 370 490 501
104 106 107 144 360 430 470 580
125 129 140 150 350 430 460 590
101 102 129 130 149 330 440 578
101 108 120 129 200 280 430 535
100 111 170 175 176 280 420 520
121 189 170 202 229 230 420 521

Expected Results: Unique Duplicate Returned across Single Row
101 107 170 280 420 430

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

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