N-th time a value occurs in a column

[elmacay]

Hi all,

I'm looking for a formula that gives me the n-th time a value occurs so far in a column.
I have in a table of about 12,000 records about 200 different values, so average of 60. I'd like to give the formula me the 'how manieth time' this value has occurred so far.

Hope this is a clear question.

Thanks for all the help.

Cheers, Elmacay

 

[Rick Rothstein]

Hi all,

I'm looking for a formula that gives me the n-th time a value occurs so far in a column.
I have in a table of about 12,000 records about 200 different values, so average of 60. I'd like to give the formula me the 'how manieth time' this value has occurred so far.

Your question is not clear to me. Are you asking to tell the formula a number, say 20, and have the formula tell you the cell where the 20th occurrence of the value is? Or do you want to specify a range and have the formula tell you how many times the value appears in that range?

 

[elmacay]

Hi Rick, thanks for your reply. This is what I'd like to do (finally got the table posting to work). A formula for column E. Hope this helps explain my question.

Book1
	A	B	C	D	E
1	Customer	Component	Combination	Countif	n-th occurrence
2	1	A	1A	2	1
3	2	B	2B	4	1
4	1	A	1A	2	2
5	2	B	2B	4	2
6	2	A	2A	3	1
7	1	B	1B	1	1
8	2	B	2B	4	3
9	2	A	2A	3	2
10	2	A	2A	3	3
11	2	B	2B	4	4
Blad1

 

[Peter_SSs]

Try this, copied down.

Excel Workbook
	A	B	C	D	E
1	Customer	Component	Combination	Countif	n-th occurrence
2	1	A	1A	2	1
3	2	B	2B	4	1
4	1	A	1A	2	2
5	2	B	2B	4	2
6	2	A	2A	3	1
7	1	B	1B	1	1
8	2	B	2B	4	3
9	2	A	2A	3	2
10	2	A	2A	3	3
11	2	B	2B	4	4
Blad1

 

[elmacay]

Of course! Works like a charm. Thanks a lot!

 

[Peter_SSs]

No problem.