Return Row Number of PREVIOUS Numeric Consecutive Duplicate in Column

  • Thread starter Sam via OfficeKB.com
  • Start date
S

Sam via OfficeKB.com

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
 
G

Guest

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
 
S

Sam via OfficeKB.com

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
 
S

Sam via OfficeKB.com

Hi bj,

Think, I've got it:

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

Cheers,
Sam
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top