Count occurrences of consecutive text

Alfie101

New Member
Joined
Jan 29, 2016
Messages
19
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:
019DAEA5-7213-4CA8-A421-C8E6696CFE10
3
019DAEA5-7213-4CA8-A421-C8E6696CFE10
3
019DAEA5-7213-4CA8-A421-C8E6696CFE10
3
019DDD1C-3563-4A32-AEDB-7109DD5C7FD2
2
019DDD1C-3563-4A32-AEDB-7109DD5C7FD2
2
01C8BF3D-217F-4219-ABE2-AD8D779BDC8C
5
01C8BF3D-217F-4219-ABE2-AD8D779BDC8C
5
01C8BF3D-217F-4219-ABE2-AD8D779BDC8C
5
01C8BF3D-217F-4219-ABE2-AD8D779BDC8C
5
01C8BF3D-217F-4219-ABE2-AD8D779BDC8C
5

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

Would greatly appreciate help with this. Thank you,
Alfie101
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,590
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jan 29, 2016
Messages
19
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
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 29, 2016
Messages
19
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
Joined
Jan 29, 2016
Messages
19
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,249
Messages
5,576,944
Members
412,753
Latest member
Coach_Olson
Top