Hi,

How can i get the exact position of a value which is the second time in a
list. For example:

Column A {1,2,2,3,3,3}
I want a formula to obtains the following result:
Column B
4 (line of the matrix for the first "3")
5 (line of the matrix for the second "3")

Do you want the *relative* position or the absolute position?

For the first *relative* position:

=MATCH(3,A2:A7,0)

For the second *relative* position assuming the values are sorted and
grouped together as in your sample:

=IF(COUNTIF(A2:A7,3)1,MATCH(3,A2:A7,0)+1,"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Hi,

How can i get the exact position of a value which is the second time in a
list. For example:

Column A {1,2,2,3,3,3}
I want a formula to obtains the following result:
Column B
4 (line of the matrix for the first "3")
5 (line of the matrix for the second "3")

Well i have a table like this:
Col A Col B Col C
Nº Nome
1 Ana
2 Guilherme
3 Gilberta 4
3 José 5
3 Sara 6
3 Filipa 7
4 Carlos
4 Daniel
4 Elizabete

and i want the line corresponding to all "3's" in column C

Can you help me?

"T. Valko" escreveu:

Do you want the *relative* position or the absolute position?

For the first *relative* position:

=MATCH(3,A2:A7,0)

For the second *relative* position assuming the values are sorted and
grouped together as in your sample:

=IF(COUNTIF(A2:A7,3)1,MATCH(3,A2:A7,0)+1,"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Hi,

How can i get the exact position of a value which is the second time in a
list. For example:

Column A {1,2,2,3,3,3}
I want a formula to obtains the following result:
Column B
4 (line of the matrix for the first "3")
5 (line of the matrix for the second "3")

Try this in C2 and copy down as needed:

=IF(A2=3,ROW(),"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Well i have a table like this:
Col A Col B Col C
Nº Nome
1 Ana
2 Guilherme
3 Gilberta 4
3 José 5
3 Sara 6
3 Filipa 7
4 Carlos
4 Daniel
4 Elizabete

and i want the line corresponding to all "3's" in column C

Can you help me?

"T. Valko" escreveu:

Do you want the *relative* position or the absolute position?

For the first *relative* position:

=MATCH(3,A2:A7,0)

For the second *relative* position assuming the values are sorted and
grouped together as in your sample:

=IF(COUNTIF(A2:A7,3)1,MATCH(3,A2:A7,0)+1,"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Hi,

How can i get the exact position of a value which is the second time in
a
list. For example:

Column A {1,2,2,3,3,3}
I want a formula to obtains the following result:
Column B
4 (line of the matrix for the first "3")
5 (line of the matrix for the second "3")

But I want to put the values that meet the criterion in another table. How
can i do?

"T. Valko" escreveu:

Try this in C2 and copy down as needed:

=IF(A2=3,ROW(),"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Well i have a table like this:
Col A Col B Col C
Nº Nome
1 Ana
2 Guilherme
3 Gilberta 4
3 José 5
3 Sara 6
3 Filipa 7
4 Carlos
4 Daniel
4 Elizabete

and i want the line corresponding to all "3's" in column C

Can you help me?

"T. Valko" escreveu:

Do you want the *relative* position or the absolute position?

For the first *relative* position:

=MATCH(3,A2:A7,0)

For the second *relative* position assuming the values are sorted and
grouped together as in your sample:

=IF(COUNTIF(A2:A7,3)1,MATCH(3,A2:A7,0)+1,"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Hi,

How can i get the exact position of a value which is the second time in
a
list. For example:

Column A {1,2,2,3,3,3}
I want a formula to obtains the following result:
Column B
4 (line of the matrix for the first "3")
5 (line of the matrix for the second "3")

Assuming you have this formula entered in column C:

=IF(A2=3,ROW(),"")

Suppose you want the data extracted starting in cell F2. Enter this formula
in F2 and copy down until you get blanks:

=IF(ROWS(F$2:F2)<=COUNT(C$2:C$10),INDEX(B$2:B$10,M ATCH(SMALL(C$2:C$10,ROWS(F$2:F2)),C$2:C$10,0)),"")


--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
But I want to put the values that meet the criterion in another table. How
can i do?

"T. Valko" escreveu:

Try this in C2 and copy down as needed:

=IF(A2=3,ROW(),"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Well i have a table like this:
Col A Col B Col C
Nº Nome
1 Ana
2 Guilherme
3 Gilberta 4
3 José 5
3 Sara 6
3 Filipa 7
4 Carlos
4 Daniel
4 Elizabete

and i want the line corresponding to all "3's" in column C

Can you help me?

"T. Valko" escreveu:

Do you want the *relative* position or the absolute position?

For the first *relative* position:

=MATCH(3,A2:A7,0)

For the second *relative* position assuming the values are sorted and
grouped together as in your sample:

=IF(COUNTIF(A2:A7,3)1,MATCH(3,A2:A7,0)+1,"")

--
Biff
Microsoft Excel MVP


"SBárbara" wrote in message
...
Hi,

How can i get the exact position of a value which is the second time
in
a
list. For example:

Column A {1,2,2,3,3,3}
I want a formula to obtains the following result:
Column B
4 (line of the matrix for the first "3")
5 (line of the matrix for the second "3")