find rows after counting data

[jts2004]

In a range A1:A100 the number 3 shows up 10 times. I have a formula to count all the 3's in the range now I want to know what rows they are on.

 

[hatman]

In cell B!: =if(a1=3,row(a1),"")
Copy down to cell b100

In cell C1: =if(iserror(small($B$1:$B$100,row(1:1)),"",small($B$1:$B$100,row(1:1))
Copy down to cell c100.

 

[hatman]

OR:

In cell B1: =MATCH(3,A1:A100,FALSE)
and in cell B2: =IF(ISERROR(MATCH(3,INDIRECT("A"&B1+1&":a100"),FALSE)),"",MATCH(3,INDIRECT("A"&B1+1&":a100"),FALSE)+B1)

copy B2 to B100.

This only works if your data actually starts at A1... if it starts on a lower row, you need a more general case.

 

[Aladin Akyurek]

In a range A1:A100 the number 3 shows up 10 times. I have a formula to count all the 3's in the range now I want to know what rows they are on.

Book1
	A	B	C	D
1	4		3
2	5		2
3	6		5
4	7		7
5	3
6	8
7	3
8	7
9	9
Sheet1

C1 is used to specify the entry of interest.

C2:

=COUNTIF(A1:A9,C1)

Determines C1's frequency of occurrence in the target range.

C3:

=IF(ROW()-ROW(C$3)+1<=$C$2,SMALL(IF($A$1:$A$9=$C$1,ROW($A$1:$A$9)),ROW()-ROW(C$3)+1),"")

which is confirmed with control+shift+enter then copied down, determines the native row location per occurrence.

 

[Yogi Anand]

Hi jts2004:

How about ...

Book1
	A	B	C	D
1	a	3	3
2	b	6
3	3	8
4	1
5	2
6	3
7	7
8	3
9	g
10
Sheet1

array formula in cell B1:B3 is ...

=SMALL(IF(A1:A10=3,ROW(A1:A10)),ROW(INDIRECT("1:"&C1)))

 

[jts2004]

Good stuff

Those all work great. Thanks

 

[Aladin Akyurek]

Re: Good stuff

Those all work great. Thanks

They are all different. And that matters...