Thread: Return Row Number of LAST Numeric Consecutive Duplicate in Column

Hi All,

Using a Dynamic Named Range "Data", I would like a Formula to return the Row
Number of the "LAST" instance of a duplicate numeric value - repeating twice
consecutively in the same column.

Thanks
Sam

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

Just to be clear, if A2:A10 contains the following data...

6
6
9
3
5
7
7
7
4

....what would be your expected result?

In article <62cefd3c3cc94@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi All,
>
> Using a Dynamic Named Range "Data", I would like a Formula to return the Row
> Number of the "LAST" instance of a duplicate numeric value - repeating twice
> consecutively in the same column.
>
> Thanks
> Sam

Hi Domenic,

A column will contain multiple instances of the same numerical value, so A2:
A10 will contain only numeric value 30. This numeric value will at times have
consecutive duplicates (x2).

Cheers,
Sam

>Domenic wrote:
>Just to be clear, if A2:A10 contains the following data...
6
6
9
3
5
7
7
7
4
>...what would be your expected result?

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

Are you saying that A2:A10 might look something like this... ?

30


30
30


30
30

If so, what result would you expect? And to be clear, will there ever
be an instance where there's three or more consecutive values?

In article <62d036ea3708e@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi Domenic,
>
> A column will contain multiple instances of the same numerical value, so A2:
> A10 will contain only numeric value 30. This numeric value will at times have
> consecutive duplicates (x2).
>
> Cheers,
> Sam

Hi Domenic,

Domenic wrote:
>Are you saying that A2:A10 might look something like this... ?

Yes

>30

>30
>30

>30
>30 Row Number of LAST 30 with two consecutive instances

>If so, what result would you expect? And to be clear, will there ever
>be an instance where there's three or more consecutive values?

I would expect the Row Number of the LAST 30 with two consecutive instances.
There may be instances with three or more consecutive values BUT I only
require the LAST Row Number of those with two consecutive instances.

Cheers,
Sam

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

First, assuming that Sheet1, Column A, starting at A2, contains the
data, change the reference for the defined named 'Data' to...

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(9.99999999999999E+307,Sheet
1!$A$2:$A$65536)+1)

Then, let B2 contain the number of interest, such as 30, and try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER...

=LOOKUP(2,1/(FREQUENCY(IF(Data=B2,ROW(Data)-MIN(ROW(Data))+1),IF(Data<>B2
,MATCH(ROW(Data),ROW(Data),0),B2+1))=2),ROW(Data))-1

Note that if you'd like to check for the last instance of 3 consecutive
values, change =2 to =3, and so on.

Hope this helps!

In article <62d0c6b91c44f@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi Domenic,
>
> Domenic wrote:
> >Are you saying that A2:A10 might look something like this... ?
>
> Yes
>
> >30
>
> >30
> >30
>
> >30
> >30 Row Number of LAST 30 with two consecutive instances
>
> >If so, what result would you expect? And to be clear, will there ever
> >be an instance where there's three or more consecutive values?
>
> I would expect the Row Number of the LAST 30 with two consecutive instances.
> There may be instances with three or more consecutive values BUT I only
> require the LAST Row Number of those with two consecutive instances.
>
> Cheers,
> Sam

Need to make a minor change. I'll post it shortly...

In article <domenic22-38E8AC.11164506072006@msnews.microsoft.com>,
Domenic <domenic22@sympatico.ca> wrote:

> First, assuming that Sheet1, Column A, starting at A2, contains the
> data, change the reference for the defined named 'Data' to...
>
> =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(9.99999999999999E+307,Sheet
> 1!$A$2:$A$65536)+1)
>
> Then, let B2 contain the number of interest, such as 30, and try the
> following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
> not just ENTER...
>
> =LOOKUP(2,1/(FREQUENCY(IF(Data=B2,ROW(Data)-MIN(ROW(Data))+1),IF(Data<>B2
> ,MATCH(ROW(Data),ROW(Data),0),B2+1))=2),ROW(Data))-1
>
> Note that if you'd like to check for the last instance of 3 consecutive
> values, change =2 to =3, and so on.
>
> Hope this helps!
>
> In article <62d0c6b91c44f@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
> wrote:
>
> > Hi Domenic,
> >
> > Domenic wrote:
> > >Are you saying that A2:A10 might look something like this... ?
> >
> > Yes
> >
> > >30
> >
> > >30
> > >30
> >
> > >30
> > >30 Row Number of LAST 30 with two consecutive instances
> >
> > >If so, what result would you expect? And to be clear, will there ever
> > >be an instance where there's three or more consecutive values?
> >
> > I would expect the Row Number of the LAST 30 with two consecutive instances.
> > There may be instances with three or more consecutive values BUT I only
> > require the LAST Row Number of those with two consecutive instances.
> >
> > Cheers,
> > Sam

Minor change...

Replace B2+1 with ROWS(Data)+1

Hope this helps!

In article <domenic22-38E8AC.11164506072006@msnews.microsoft.com>,
Domenic <domenic22@sympatico.ca> wrote:

> First, assuming that Sheet1, Column A, starting at A2, contains the
> data, change the reference for the defined named 'Data' to...
>
> =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(9.99999999999999E+307,Sheet
> 1!$A$2:$A$65536)+1)
>
> Then, let B2 contain the number of interest, such as 30, and try the
> following formula which needs to be confirmed with CONTROL+SHIFT+ENTER,
> not just ENTER...
>
> =LOOKUP(2,1/(FREQUENCY(IF(Data=B2,ROW(Data)-MIN(ROW(Data))+1),IF(Data<>B2
> ,MATCH(ROW(Data),ROW(Data),0),B2+1))=2),ROW(Data))-1
>
> Note that if you'd like to check for the last instance of 3 consecutive
> values, change =2 to =3, and so on.
>
> Hope this helps!

Hi Domenic,

Thank you for your time and assistance.

Your Formulae does work but I forgot to say the Dynamic Named Range "Data"
spans many columns. Based on the orginal scenario I would like to have the
relevant Row Number from these columns also returned. Can your Formulae be
adjusted to facilitate this.

Apologies for incomplete explanation.

Cheers,
Sam

Domenic wrote:
>Minor change...

>Replace B2+1 with ROWS(Data)+1

>Hope this helps!

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

Let's assume that Sheet1, Columns A, B, and C, starting at Row 2,
contains the data, let E2 contain the value of interest, such as 30,
then try the following...

Select F2

Insert > Name > Define

Name: Data2

Refers to:

=Sheet1!A$2:INDEX(Sheet1!A$2:A$65536,MATCH(9.99999999999999E+307,Sheet1!A
$2:A$65536)+1)

Click OK

Change the references accordingly. Then, enter the following formula in
F2, and copy across:

=LOOKUP(2,1/(FREQUENCY(IF(Data2=$E2,ROW(Data2)-MIN(ROW(Data2))+1),IF(Data
2<>$E2,MATCH(ROW(Data2),ROW(Data2),0),ROWS(Data2)+1))=2),ROW(Data2))-1

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <62d9abfc700a8@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi Domenic,
>
> Thank you for your time and assistance.
>
> Your Formulae does work but I forgot to say the Dynamic Named Range "Data"
> spans many columns. Based on the orginal scenario I would like to have the
> relevant Row Number from these columns also returned. Can your Formulae be
> adjusted to facilitate this.
>
> Apologies for incomplete explanation.
>
> Cheers,
> Sam

Hi Domenic,

Thank you very much for all your help. The Formulae works Great!

Cheers,
Sam

Domenic wrote:
>Let's assume that Sheet1, Columns A, B, and C, starting at Row 2,
>contains the data, let E2 contain the value of interest, such as 30,
>then try the following...
>
>Select F2
>
>Insert > Name > Define
>
>Name: Data2
>
>Refers to:
>
>=Sheet1!A$2:INDEX(Sheet1!A$2:A$65536,MATCH(9.99999999999999E+307,Sheet1!A
>$2:A$65536)+1)
>
>Click OK
>
>Change the references accordingly. Then, enter the following formula in
>F2, and copy across:
>
>=LOOKUP(2,1/(FREQUENCY(IF(Data2=$E2,ROW(Data2)-MIN(ROW(Data2))+1),IF(Data
>2<>$E2,MATCH(ROW(Data2),ROW(Data2),0),ROWS(Data2)+1))=2),ROW(Data2))-1
>
>...confirmed with CONTROL+SHIFT+ENTER.
>
>Hope this helps!
>
>> Hi Domenic,
>>
>[quoted text clipped - 9 lines]
>> Cheers,
>> Sam

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