Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate 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 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
Hi Ron,
Thanks very much for reply and further input. Ron Coderre wrote: The issue must be data related. Are there Blanks? Text? No Blanks, text only in Row "1" . 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 No text, although I did have a problem copying back my original Sample Data using Data Text to Columns which created the #N/A errors but sorted now. However, regarding my Sample Data and the Expected Results, I've noticed that your Formula picks out Numeric Value 102 as a consecutive duplicate value in the SAME column. It is a duplicate in the same column but NOT a consecutive duplicate as described in my original post. It should not be included in the results. 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,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? Numeric Value 102 is NOT a consecutive duplicate as described in my original post. However, the Formula does incorrectly return Numeric Value 102. I think this could be part of the problem and solution. *********** Regards, Ron XL2002, WinXP Cheers, Sam -- Message posted via http://www.officekb.com |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
Well, the formulas didn't get any prettier, but....I *think* this works.....
A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$8=$A$3:$H$9)*($A $2:$H$8+COLUMN($A$2:$H$8)*1000)),($A$2:$H$8+COLUMN ($A$2:$H$8)*1000))=1)*ROW($A$1:INDEX($A:$A,ROWS($ A$2:$H$8)*COLUMNS($A$2:$H$8)+1)),0),COLUMNS($A$11: A11)) A12: =IF(A11,INDEX($A$2:$H$8,CEILING(A11/8,1),MOD(A11-1,8)+1),"") Are we done yet? *********** Regards, Ron XL2002, WinXP "Sam via OfficeKB.com" wrote: Hi Ron, Thanks very much for reply and further input. Ron Coderre wrote: The issue must be data related. Are there Blanks? Text? No Blanks, text only in Row "1" . 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 No text, although I did have a problem copying back my original Sample Data using Data Text to Columns which created the #N/A errors but sorted now. However, regarding my Sample Data and the Expected Results, I've noticed that your Formula picks out Numeric Value 102 as a consecutive duplicate value in the SAME column. It is a duplicate in the same column but NOT a consecutive duplicate as described in my original post. It should not be included in the results. 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,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? Numeric Value 102 is NOT a consecutive duplicate as described in my original post. However, the Formula does incorrectly return Numeric Value 102. I think this could be part of the problem and solution. *********** Regards, Ron XL2002, WinXP Cheers, Sam -- Message posted via http://www.officekb.com |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
Done?....not quite. The previous formula calc'd consecutive blanks as dupes.
This fixes that: A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$7<0)*($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))=1)*ROW($A$1:IND EX($A:$A,ROWS($A$2:$H$7)*COLUMNS($A$2:$H$7)+1)),0) ,COLUMNS($A$11:A11)) How're we doing? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well, the formulas didn't get any prettier, but....I *think* this works..... A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$8=$A$3:$H$9)*($A $2:$H$8+COLUMN($A$2:$H$8)*1000)),($A$2:$H$8+COLUMN ($A$2:$H$8)*1000))=1)*ROW($A$1:INDEX($A:$A,ROWS($ A$2:$H$8)*COLUMNS($A$2:$H$8)+1)),0),COLUMNS($A$11: A11)) A12: =IF(A11,INDEX($A$2:$H$8,CEILING(A11/8,1),MOD(A11-1,8)+1),"") Are we done yet? *********** Regards, Ron XL2002, WinXP "Sam via OfficeKB.com" wrote: Hi Ron, Thanks very much for reply and further input. Ron Coderre wrote: The issue must be data related. Are there Blanks? Text? No Blanks, text only in Row "1" . 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 No text, although I did have a problem copying back my original Sample Data using Data Text to Columns which created the #N/A errors but sorted now. However, regarding my Sample Data and the Expected Results, I've noticed that your Formula picks out Numeric Value 102 as a consecutive duplicate value in the SAME column. It is a duplicate in the same column but NOT a consecutive duplicate as described in my original post. It should not be included in the results. 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,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? Numeric Value 102 is NOT a consecutive duplicate as described in my original post. However, the Formula does incorrectly return Numeric Value 102. I think this could be part of the problem and solution. *********** Regards, Ron XL2002, WinXP Cheers, Sam -- Message posted via http://www.officekb.com |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
It's picking up the 100 in A7.
Biff "Ron Coderre" wrote in message ... Done?....not quite. The previous formula calc'd consecutive blanks as dupes. This fixes that: A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$7<0)*($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))=1)*ROW($A$1:IND EX($A:$A,ROWS($A$2:$H$7)*COLUMNS($A$2:$H$7)+1)),0) ,COLUMNS($A$11:A11)) How're we doing? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well, the formulas didn't get any prettier, but....I *think* this works..... A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$8=$A$3:$H$9)*($A $2:$H$8+COLUMN($A$2:$H$8)*1000)),($A$2:$H$8+COLUMN ($A$2:$H$8)*1000))=1)*ROW($A$1:INDEX($A:$A,ROWS($ A$2:$H$8)*COLUMNS($A$2:$H$8)+1)),0),COLUMNS($A$11: A11)) A12: =IF(A11,INDEX($A$2:$H$8,CEILING(A11/8,1),MOD(A11-1,8)+1),"") Are we done yet? *********** Regards, Ron XL2002, WinXP "Sam via OfficeKB.com" wrote: Hi Ron, Thanks very much for reply and further input. Ron Coderre wrote: The issue must be data related. Are there Blanks? Text? No Blanks, text only in Row "1" . 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 No text, although I did have a problem copying back my original Sample Data using Data Text to Columns which created the #N/A errors but sorted now. However, regarding my Sample Data and the Expected Results, I've noticed that your Formula picks out Numeric Value 102 as a consecutive duplicate value in the SAME column. It is a duplicate in the same column but NOT a consecutive duplicate as described in my original post. It should not be included in the results. 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,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? Numeric Value 102 is NOT a consecutive duplicate as described in my original post. However, the Formula does incorrectly return Numeric Value 102. I think this could be part of the problem and solution. *********** Regards, Ron XL2002, WinXP Cheers, Sam -- Message posted via http://www.officekb.com |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
Hi Ron,
Using the Sample Data the results do not tie up with the Expected Results - Now Numeric Value 100 is being listed. Expected Results: Unique Consecutive Duplicate Returned across Single Row in ascending order. 101 107 170 280 420 430 Column 1 = 101 Column 3 = 107, 170 Column 6 = 280, 430 Column 7 = 420 Ron Coderre wrote: Done?....not quite. The previous formula calc'd consecutive blanks as dupes. This fixes that: A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$7<0)*($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))=1)*ROW($A$1:IN DEX($A:$A,ROWS($A$2:$H$7)*COLUMNS($A$2:$H$7)+1)),0 ),COLUMNS($A$11:A11)) How're we doing? Not quite there, yet. *********** Regards, Ron XL2002, WinXP Well, the formulas didn't get any prettier, but....I *think* this works..... Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
Hi Biff,
Thanks, bit slow in seeing your post. Cheers, Sam T. Valko wrote: It's picking up the 100 in A7. Biff -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
OK....Here's the latest in a series of final formulas : \
A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$7<0)*($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 In ascending order that would be: 101 107 170 280 420 430 Dare I ask? *********** Regards, Ron XL2002, WinXP "Sam via OfficeKB.com" wrote: Hi Ron, Using the Sample Data the results do not tie up with the Expected Results - Now Numeric Value 100 is being listed. Expected Results: Unique Consecutive Duplicate Returned across Single Row in ascending order. 101 107 170 280 420 430 Column 1 = 101 Column 3 = 107, 170 Column 6 = 280, 430 Column 7 = 420 Ron Coderre wrote: Done?....not quite. The previous formula calc'd consecutive blanks as dupes. This fixes that: A11: =LARGE(INDEX((FREQUENCY((($A$2:$H$7<0)*($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))=1)*ROW($A$1:IN DEX($A:$A,ROWS($A$2:$H$7)*COLUMNS($A$2:$H$7)+1)),0 ),COLUMNS($A$11:A11)) How're we doing? Not quite there, yet. *********** Regards, Ron XL2002, WinXP Well, the formulas didn't get any prettier, but....I *think* this works..... Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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$7<0)*($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 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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$7<0)*($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 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Unique Consecutive Duplicate Values across Single Row
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
duplicate numbers in column a and diferent values in b | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
Search multiple values to return single values | Excel Worksheet Functions |