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 duplicate could be in any column.

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

Sample Data Layout:

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

Expected Results: Unique Duplicate Returned across Single Row
101 102 129 130 430



Thanks,
Sam

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

On Sat, 20 Jan 2007 18:07:22 GMT, "Sam via OfficeKB.com" <u4102@uwe wrote:

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 duplicate could be in any column.

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

Sample Data Layout:

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

Expected Results: Unique Duplicate Returned across Single Row
101 102 129 130 430



Thanks,
Sam

Here's one method, assuming you have fewer than 256 entries.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

NAME your data range: rng

Enter this **array** formula in some cell. Copy/drag to the right at least as
far as required. (It will return blanks if you copy too far, so no harm).

To enter an array formula, after entering the formula, hold down <ctrl<shift
while hitting <enter. Excel will place braces {...} around the formula:

=INDEX(UNIQUEVALUES((IF(COUNTIF(rng,rng)1,rng)),1 ),
COLUMNS($A:A)+NOT(ISNUMBER(UNIQUEVALUES((
IF(COUNTIF(rng,rng)1,rng)),1))))


--ron

Assume you want the results starting in cell A12 and across.

Array entered in A12:

=SMALL(IF(COUNTIF(Data,Data)1,Data),1)

Array entered in B12 then copied across until you get blanks:

=IF(MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,Data)) )=0,"",MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,Dat a))))

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6c938087306a5@uwe...
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 duplicate could be in any column.

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

Sample Data Layout:

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

Expected Results: Unique Duplicate Returned across Single Row
101 102 129 130 430



Thanks,
Sam

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

Array entered in A12:
=SMALL(IF(COUNTIF(Data,Data)1,Data),1)

Here's a tweak that adds an error trap in case there are no duplicates:

=IF(MAX(COUNTIF(Data,Data))1,MIN(IF(COUNTIF(Data, Data)1,Data)),"")

Biff

"T. Valko" wrote in message
...
Assume you want the results starting in cell A12 and across.

Array entered in A12:

=SMALL(IF(COUNTIF(Data,Data)1,Data),1)

Array entered in B12 then copied across until you get blanks:

=IF(MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,Data)) )=0,"",MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,Dat a))))

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6c938087306a5@uwe...
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 duplicate could be in any column.

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

Sample Data Layout:

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

Expected Results: Unique Duplicate Returned across Single Row
101 102 129 130 430



Thanks,
Sam

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

Hi Ron,

Thank you very much for your assistance. Laurent Longre's Excellent MoreFunc
add-in and your Great Array Formula provides the required results. Very much
appreciated!

Cheers,
Sam


Ron Rosenfeld wrote:

Here's one method, assuming you have fewer than 256 entries.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

NAME your data range: rng

Enter this **array** formula in some cell. Copy/drag to the right at least as
far as required. (It will return blanks if you copy too far, so no harm).

To enter an array formula, after entering the formula, hold down <ctrl<shift
while hitting <enter. Excel will place braces {...} around the formula:

=INDEX(UNIQUEVALUES((IF(COUNTIF(rng,rng)1,rng)), 1),
COLUMNS($A:A)+NOT(ISNUMBER(UNIQUEVALUES((
IF(COUNTIF(rng,rng)1,rng)),1))))

--ron

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

Hi Biff,

Thank you very much indeed for your assistance. Brilliant Formula solution
works Great!

Cheers,
Sam

T. Valko wrote:
Here's a tweak that adds an error trap in case there are no duplicates:

Array entered in A12:

=IF(MAX(COUNTIF(Data,Data))1,MIN(IF(COUNTIF(Data ,Data)1,Data)),"")

Array entered in B12 then copied across until you get blanks:

=IF(MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,Data) ))=0,"",MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,Da ta))))

Biff

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

On Sat, 20 Jan 2007 23:36:47 GMT, "Sam via OfficeKB.com" <u4102@uwe wrote:

Hi Ron,

Thank you very much for your assistance. Laurent Longre's Excellent MoreFunc
add-in and your Great Array Formula provides the required results. Very much
appreciated!

Cheers,
Sam



You're welcome.

Thanks for the feedback.
--ron

You're welcome. Thanks for the feedback!

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6c9677aef4055@uwe...
Hi Biff,

Thank you very much indeed for your assistance. Brilliant Formula solution
works Great!

Cheers,
Sam

T. Valko wrote:
Here's a tweak that adds an error trap in case there are no duplicates:

Array entered in A12:

=IF(MAX(COUNTIF(Data,Data))1,MIN(IF(COUNTIF(Dat a,Data)1,Data)),"")

Array entered in B12 then copied across until you get blanks:

=IF(MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,Data )))=0,"",MIN(IF(COUNTIF(Data,Data)1,IF(DataA12,D ata))))

Biff

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