N-th time a value occurs in a column

elmacay

Board Regular
Joined
May 4, 2006
Messages
88
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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?
 
Upvote 0
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
ABCDE
1CustomerComponentCombinationCountifn-th occurrence
21A1A21
32B2B41
41A1A22
52B2B42
62A2A31
71B1B11
82B2B43
92A2A32
102A2A33
112B2B44
Blad1
 
Upvote 0
Try this, copied down.

Excel Workbook
ABCDE
1CustomerComponentCombinationCountifn-th occurrence
21A1A21
32B2B41
41A1A22
52B2B42
62A2A31
71B1B11
82B2B43
92A2A32
102A2A33
112B2B44
Blad1
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top