Hi All,

Using a Dynamic Named Range "Data", spanning many rows and one column; I
would like a Formula to return the Row Number of the "PREVIOUS" instance of a
duplicate numeric value - repeating once consecutively in the same column; i.
e. two instances of the same value one after the other.

The first row, row number 2 holds the oldest data, and the most recent data
is in the last row, row number 27.
Dynamic Named Range "Data" Refers To: =OFFSET(Quarterly!$I$2,0,0,COUNT
(Quarterly!$I:$I),1)

Sample Data:
Col "C"
Row 2 134
Row 3 11
Row 4 130
Row 5 131
Row 6 137
Row 7 128
Row 8 11
Row 9 11
Row 10 148
Row 11 126
Row 12 137
Row 13 122
Row 14 111
Row 15 123
Row 16 120
Row 17 120
Row 18 133
Row 19 140
Row 20 14
Row 21 17
Row 22 140
Row 23 112
Row 24 122
Row 25 132
Row 26 18
Row 27 144

NB: The last consecutive (x1) repeat is numeric value 120 - row number 17.

Expected Result:
The PREVIOUS consecutive (x1) repeat is numeric value 11 - row number 9.
However, to accommodate other referencing, I would like row number 8 returned
as the correct result.

Thanks,
Sam

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

try
=LARGE(ROW(C2:C100)*(C2:C100=C3:C101)*(C2:C101<"" ),2)
entered as an array control-shift-enter
change the bottom if 100(101) is not enough
to find the last instead of the penultimate one use 1 instead for the end
number

"Sam via OfficeKB.com" wrote:

Hi All,

Using a Dynamic Named Range "Data", spanning many rows and one column; I
would like a Formula to return the Row Number of the "PREVIOUS" instance of a
duplicate numeric value - repeating once consecutively in the same column; i.
e. two instances of the same value one after the other.

The first row, row number 2 holds the oldest data, and the most recent data
is in the last row, row number 27.
Dynamic Named Range "Data" Refers To: =OFFSET(Quarterly!$I$2,0,0,COUNT
(Quarterly!$I:$I),1)

Sample Data:
Col "C"
Row 2 134
Row 3 11
Row 4 130
Row 5 131
Row 6 137
Row 7 128
Row 8 11
Row 9 11
Row 10 148
Row 11 126
Row 12 137
Row 13 122
Row 14 111
Row 15 123
Row 16 120
Row 17 120
Row 18 133
Row 19 140
Row 20 14
Row 21 17
Row 22 140
Row 23 112
Row 24 122
Row 25 132
Row 26 18
Row 27 144

NB: The last consecutive (x1) repeat is numeric value 120 - row number 17.

Expected Result:
The PREVIOUS consecutive (x1) repeat is numeric value 11 - row number 9.
However, to accommodate other referencing, I would like row number 8 returned
as the correct result.

Thanks,
Sam

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

Hi bj,

Thank you very much for reply and assistance. Your Formula does the job.
Great!

=LARGE(ROW(C2:C100)*(C2:C100=C3:C101)*(C2:C101<" "),2)

corrected typo in the last range
=LARGE(ROW(C2:C100)*(C2:C100=C3:C101)*(C2:C100<"" ),2)

However, can you provide a version using the actual Dynamic Name "Data",
rather than the column and row references. Further assistance appreciated.

Cheers,
Sam

bj wrote:
try
=LARGE(ROW(C2:C100)*(C2:C100=C3:C101)*(C2:C101<" "),2)
entered as an array control-shift-enter
change the bottom if 100(101) is not enough
to find the last instead of the penultimate one use 1 instead for the end
number

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

Hi bj,

Think, I've got it:

=LARGE(ROW(Data)*(Data=OFFSET(Data,1,0))*(Data<"" ),2)

Cheers,
Sam

Sam wrote:
Hi bj,

Thank you very much for reply and assistance. Your Formula does the job.
Great!

=LARGE(ROW(C2:C100)*(C2:C100=C3:C101)*(C2:C101< ""),2)

corrected typo in the last range
=LARGE(ROW(C2:C100)*(C2:C100=C3:C101)*(C2:C100<" "),2)

However, can you provide a version using the actual Dynamic Name "Data",
rather than the column and row references. Further assistance appreciated.

Cheers,
Sam

try
=LARGE(ROW(C2:C100)*(C2:C100=C3:C101)*(C2:C101< ""),2)
entered as an array control-shift-enter
change the bottom if 100(101) is not enough
to find the last instead of the penultimate one use 1 instead for the end
number

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