Count occurrences of consecutive text

Alfie101

New Member
Hi,
Is there a formula to count occurrences of consecutive text in a lengthy file, and put the number in an adjacent column, as in the sample below:

<colgroup><col><col></colgroup><tbody>
</tbody>

Would greatly appreciate help with this. Thank you,
Alfie101

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

steve the fish

Well-known Member
Is this a sorted list? If so you can just do a countif. If not it will be more complex and right now i cant think how you could do that.

Rick Rothstein

MrExcel MVP
Is this a sorted list? If so you can just do a countif. If not it will be more complex and right now i cant think how you could do that.
It does not matter whether the list is sorted or not, COUNTIF is still the way to go. Assuming the first value is in cell A1...

=COUNTIF(A\$1:A\$10,A1)

Where the red highlighted number is the last row number with data in it.

Alfie101

New Member
Is this a sorted list? If so you can just do a countif. If not it will be more complex and right now i cant think how you could do that.

Hi Steve,

I apologize, to explain: the sample is what I hope to end up with. That is, with the current list of text items (column 1), ordered by the text items in that field, and with a count of the consecutive occurrences of column 1 entered into column 2.

Thank you, hope that is clear,

Alfie101

steve the fish

Well-known Member

I was thinking it may matter that if the value reoccured elsewhere in the list it started a new count. If that isnt the case then of course it doesnt matter if the list is sorted.

Alfie101

New Member
It does not matter whether the list is sorted or not, COUNTIF is still the way to go. Assuming the first value is in cell A1...

=COUNTIF(A\$1:A\$10,A1)

Where the red highlighted number is the last row number with data in it.

Worked a charm. Thank you very much Rick, regards Alfie101
Thank you also Steve, appreciated, Alfie101

Alfie101

New Member
Hi Steve,

I apologize, to explain: the sample is what I hope to end up with. That is, with the current list of text items (column 1), ordered by the text items in that field, and with a count of the consecutive occurrences of column 1 entered into column 2.

Thank you, hope that is clear,

Alfie101
Thank you Steve, I have it fixed now, appreciate your help, Alfie101

Replies
3
Views
318
Replies
10
Views
258
Replies
11
Views
213
Replies
5
Views
381
Replies
4
Views
214