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 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


Thanks,
Sam

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

Sub sam()
Dim s As String
For i = 1 To 8
For j = 2 To 7
v = Cells(j, i).Value
If Cells(j - 1, i).Value = v Then
If s = "" Then
s = v
Else
s = s & "," & v
End If
MsgBox ("column " & i & " " & v)
End If
Next
Next
MsgBox (s)
End Sub

gives result in MSGBOX format. You can modify the code if you need the
results put back into the worksheet.
--
Gary's Student
gsnu200703


"Sam via OfficeKB.com" 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 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


Thanks,
Sam

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

Hi Gary's Student,

Thank you very much for your time and assistance. That's Great! Provides the
desired results.

What do I need to modify to put the results back into the worksheet?

Cheers,
Sam



Gary''s Student wrote:
Sub sam()
Dim s As String
For i = 1 To 8
For j = 2 To 7
v = Cells(j, i).Value
If Cells(j - 1, i).Value = v Then
If s = "" Then
s = v
Else
s = s & "," & v
End If
MsgBox ("column " & i & " " & v)
End If
Next
Next
MsgBox (s)
End Sub

gives result in MSGBOX format. You can modify the code if you need the
results put back into the worksheet.

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

Not that this is better than the VBA solution posted.....
but, since you posted in the worksheet functions group,
and I felt like a challenge...
try this:

With your posted list in A1:H7

This formula locates the duplicate items in the grid
A10:
=LARGE(INDEX((FREQUENCY(($A$1:$H$7+COLUMN($A$1:$H$ 7)*1000),($A$1:$H$7+COLUMN($A$1:$H$7)*1000))1)*RO W($A$1:INDEX($A:$A,COUNT($A$1:$H$7)+1)),0),COLUMNS ($A$10:A10))

Copy that formula across to the right, 10 columns or so

This formula translates the location reference to an item in the grid
A11: =INDEX($A$1:$H$7,CEILING(A10/8,1),MOD(A10-1,8)+1)
Copy that formula across to the right also

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Sam via OfficeKB.com" 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 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


Thanks,
Sam

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

Hi Ron,

Thank you for your time and assistance. Unfortunately, I am not getting the
expected results. I receive #N/A in the cells using this Formula:

=LARGE(INDEX((FREQUENCY(($A$1:$H$7+COLUMN($A$1:$H $7)*1000),($A$1:$H$7+COLUMN($A$1:$H$7)*1000))1)*R OW($A$1:INDEX($A:$A,COUNT($A$1:$H$7)+1)),0),COLUMN S($A$10:A10))

This formula locates the duplicate items in the grid A10:

Copy that formula across to the right, 10 columns or so

My Data starts in Column "D", Row "2". Row "1" has Text Labels. I think I've
made the necessary adjustments for that but I'm still getting #N/A.

Further help appreciated.

Cheers,
Sam

Ron Coderre wrote:
Not that this is better than the VBA solution posted.....
but, since you posted in the worksheet functions group,
and I felt like a challenge...
try this:

With your posted list in A1:H7

This formula locates the duplicate items in the grid
A10:
=LARGE(INDEX((FREQUENCY(($A$1:$H$7+COLUMN($A$1:$H $7)*1000),($A$1:$H$7+COLUMN($A$1:$H$7)*1000))1)*R OW($A$1:INDEX($A:$A,COUNT($A$1:$H$7)+1)),0),COLUMN S($A$10:A10))

Copy that formula across to the right, 10 columns or so

This formula translates the location reference to an item in the grid
A11: =INDEX($A$1:$H$7,CEILING(A10/8,1),MOD(A10-1,8)+1)
Copy that formula across to the right also

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP

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

Not sure why you're getting errors.....

I used your posted data table, beginning in A1
Maybe you have column headings in Row_1?

If that's the case and the data range is in A2:H8
then these are the formulas

A11:
=LARGE(INDEX((FREQUENCY(($A$2:$H$8+COLUMN($A$2:$H$ 8)*1000),($A$2:$H$8+COLUMN($A$2:$H$8)*1000))1)*RO W($A$1:INDEX($A:$A,COUNT($A$2:$H$8)+1)),0),COLUMNS ($A$11:A11))

A12: =INDEX($A$2:$H$8,CEILING(A11/8,1),MOD(A11-1,8)+1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Sam via OfficeKB.com" wrote:

Hi Ron,

Thank you for your time and assistance. Unfortunately, I am not getting the
expected results. I receive #N/A in the cells using this Formula:

=LARGE(INDEX((FREQUENCY(($A$1:$H$7+COLUMN($A$1:$H $7)*1000),($A$1:$H$7+COLUMN($A$1:$H$7)*1000))1)*R OW($A$1:INDEX($A:$A,COUNT($A$1:$H$7)+1)),0),COLUMN S($A$10:A10))

This formula locates the duplicate items in the grid A10:

Copy that formula across to the right, 10 columns or so

My Data starts in Column "D", Row "2". Row "1" has Text Labels. I think I've
made the necessary adjustments for that but I'm still getting #N/A.

Further help appreciated.

Cheers,
Sam

Ron Coderre wrote:
Not that this is better than the VBA solution posted.....
but, since you posted in the worksheet functions group,
and I felt like a challenge...
try this:

With your posted list in A1:H7

This formula locates the duplicate items in the grid
A10:
=LARGE(INDEX((FREQUENCY(($A$1:$H$7+COLUMN($A$1:$H $7)*1000),($A$1:$H$7+COLUMN($A$1:$H$7)*1000))1)*R OW($A$1:INDEX($A:$A,COUNT($A$1:$H$7)+1)),0),COLUMN S($A$10:A10))

Copy that formula across to the right, 10 columns or so

This formula translates the location reference to an item in the grid
A11: =INDEX($A$1:$H$7,CEILING(A10/8,1),MOD(A10-1,8)+1)
Copy that formula across to the right also

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP

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

Hi Ron,

Thanks for reply. Even with Sample Data starting in cell A1 and using your
original version of the Formula I still get #N/A?

Ron Coderre wrote:
Not sure why you're getting errors.....

If anything comes to mind would appreciate further help.

I used your posted data table, beginning in A1
Maybe you have column headings in Row_1?

Yes, I just mentioned in my previous post my Data starts in Column "D", Row
"2". Row "1" has Text Labels. I think I've made the necessary adjustments for
that but I'm still getting #N/A.

If that's the case and the data range is in A2:H8
then these are the formulas

A11:
=LARGE(INDEX((FREQUENCY(($A$2:$H$8+COLUMN($A$2:$H $8)*1000),($A$2:$H$8+COLUMN($A$2:$H$8)*1000))1)*R OW($A$1:INDEX($A:$A,COUNT($A$2:$H$8)+1)),0),COLUMN S($A$11:A11))

A12: =INDEX($A$2:$H$8,CEILING(A11/8,1),MOD(A11-1,8)+1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Cheers,
Sam

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

The issue must be data related. Are there Blanks? Text?
Those are the only exceptions that throw errors in my testing.

With the data in A2:H11, this formula is durable against blanks, but not text

A11:
=LARGE(INDEX((FREQUENCY(($A$2:$H$8+COLUMN($A$2:$H$ 8)*1000),($A$2:$H$8+COLUMN($A$2:$H$8)*1000))1)*RO W($A$1:INDEX($A:$A,ROWS($A$2:$H$8)*COLUMNS($A$2:$H $8)+1)),0),COLUMNS($A$11:A11))

Does that help?
or.... do you see anything else that may be an issue?

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

XL2002, WinXP


"Sam via OfficeKB.com" wrote:

Hi Ron,

Thanks for reply. Even with Sample Data starting in cell A1 and using your
original version of the Formula I still get #N/A?

Ron Coderre wrote:
Not sure why you're getting errors.....

If anything comes to mind would appreciate further help.

I used your posted data table, beginning in A1
Maybe you have column headings in Row_1?

Yes, I just mentioned in my previous post my Data starts in Column "D", Row
"2". Row "1" has Text Labels. I think I've made the necessary adjustments for
that but I'm still getting #N/A.

If that's the case and the data range is in A2:H8
then these are the formulas

A11:
=LARGE(INDEX((FREQUENCY(($A$2:$H$8+COLUMN($A$2:$H $8)*1000),($A$2:$H$8+COLUMN($A$2:$H$8)*1000))1)*R OW($A$1:INDEX($A:$A,COUNT($A$2:$H$8)+1)),0),COLUMN S($A$11:A11))

A12: =INDEX($A$2:$H$8,CEILING(A11/8,1),MOD(A11-1,8)+1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Cheers,
Sam

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

Assuming "data" is in the range A1:H7.

Array entered:

=IF(ISERROR(SMALL(IF($A1:$H6=$A2:$H7,$A1:$H6),COLU MNS($A:A))),"",SMALL(IF($A1:$H6=$A2:$H7,$A1:$H6),C OLUMNS($A:A)))

Copy across until you get blanks.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6d50abbcea48c@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 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


Thanks,
Sam

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

Well, maybe not!

That formula works ok on the sample you posted but when I put it through the
"wringer" it chokes!

For example: (I guess this is possible?):

100...101
100...101
101...110
101...120
101...107

Biff

"T. Valko" wrote in message
...
Assuming "data" is in the range A1:H7.

Array entered:

=IF(ISERROR(SMALL(IF($A1:$H6=$A2:$H7,$A1:$H6),COLU MNS($A:A))),"",SMALL(IF($A1:$H6=$A2:$H7,$A1:$H6),C OLUMNS($A:A)))

Copy across until you get blanks.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6d50abbcea48c@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 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


Thanks,
Sam

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

Hi Biff,

Thank you for your time and assistance.


T. Valko wrote:
Well, maybe not!

That formula works ok on the sample you posted but when I put it through the
"wringer" it chokes!

For example: (I guess this is possible?):

Yes, it is possible.

100...101
100...101
101...110
101...120
101...107

Biff

Cheers,
Sam

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

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

J2:

=SUM(IF(FREQUENCY(IF(A2:H7=A3:H8,A2:H7),IF(A2:H7=A 3: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!

In article <6d50abbcea48c@uwe, "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 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


Thanks,
Sam

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