detection of rows of the duplicated names in a table

[masoud12]

i have table. some names of the first row are duplicated. i sort the names now i want to detect the first and last row number for the duplicated names.

sample1.xlsx
	A	B	C	D
1		count	forst row	last row
2	peter	1	2	3
3	peter	1	2	3
4	john	2	4	4
5	ali	3	5	5
6	mori	4	6	8
7	mori	4	6	8
8	mori	4	6	8
9	anderson	5	9	9
10	caroline	6	10	10
11	pele	7	11	12
12	pele	7	11	12
Sheet1

 

[masoud12]

so i have the first column and i want the last three column. or even only last column.

 

[Anonymous1378]

Try the following:

MAX(IF(A$2:A$12=A2,ROW(A$2:A$12),0))

 

[masoud12]

i use it but my file is very heavy

Try the following:

MAX(IF(A$2:A$12=A2,ROW(A$2:A$12),0))

 

[Anonymous1378]

I'm not sure if this is any better but give it a go?

=LOOKUP(2,1/($A$2:$A$12=A2),ROW($A$2:$A$12))

 

[masoud12]

I'm not sure if this is any better but give it a go?

=LOOKUP(2,1/($A$2:$A$12=A2),ROW($A$2:$A$12))

thank you Anonymous1378

​

 

[Peter_SSs]

Try these. Should not be much overhead.

23 07 31.xlsm
	A		C	D
1			first	last
2	peter		2	3
3	peter		2	3
4	john		4	4
5	ali		5	5
6	mori		6	8
7	mori		6	8
8	mori		6	8
9	anderson		9	9
10	carolins		10	10
11	pele		11	12
12	pele		11	12
First Last Rows

Cell Formulas
Range	Formula
C2:C12	C2	=IF(A2=A1,C1,ROW())
D2:D12	D2	=IF(A2=A3,D3,ROW())