|
Return Unique Duplicate Numeric Values across Single Row
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 |
Return Unique Duplicate Numeric Values across Single Row
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 |
Return Unique Duplicate Numeric Values across Single Row
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 |
Return Unique Duplicate Numeric Values across Single Row
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 |
Return Unique Duplicate Numeric Values across Single Row
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 |
Return Unique Duplicate Numeric Values across Single Row
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 |
Return Unique Duplicate Numeric Values across Single Row
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 |
Return Unique Duplicate Numeric Values across Single Row
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 |
| All times are GMT +1. The time now is 11:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com