Getting counts of rows that match specific regex strings between other regex strings

[IOO]

I have a column with several names that all begin with uppercase letters and between these names are identifiers that each name is associated with. These identifiers all start with a lowercase letter. The names and identifiers vary with each raw data that are uploaded to this sheet. I'm trying to put together a macro/VBA script using regex and don't know how to go about it. I assume using regex ^[A-Z]* and ^[a-z]* to capture the names and identifiers respectively would be a good start to automate how to get the count of these identifiers and put the count to the cell next to each corresponding name:

For example: In the attached image, the yellow highlights are the identifiers and the names are in bold text, all in column B. The bottom sheet in the image shows what I'm trying to achieve. The counts (in red texts) of the identifiers for each names put in the cell next to the name. I will appreciate help with a code.

 

[mse330]

A late hello & welcome to Mr. Excel, IOO

I was searching something & came across your post which is not answered yet. I don't know if you're still looking for a solution or not but I think the below code would achieve your goal without the need to RegEx

Sub CntKeys()

Dim j&

For x = Range("B" & Rows.Count).End(xlUp).Row To 4 Step -1
    If Left(Cells(x, 2), 1) Like "[A-Z]" Then
        Cells(x, 3) = j
        j = 0
    Else
        j = j + 1
    End If
Next

End Sub

Book1
	A	B	C
1
2
3	Group	Name	ID Count
4	Team A	Tracy	2
5		z12390
6		d76653
7	C Team	Richard	1
8		p56723
9		Timothy	1
10		t3304067
11		Freddy	1
12		twest12
13	F Team	Nancy	1
14		d021300
15		Dina	2
16		llfinn
17		a3478u
Sheet2

 

[Peter_SSs]

.. or use standard worksheet functions. Just ensure where I have used $100 you use a row number that will definitely be as far as your last data.

20 05 10.xlsm
	A	B	C
1
2
3	Group	Name	ID Count
4	Team A	Tracy	2
5		z12390
6		d76653
7	C Team	Richard	1
8		p56723
9		Timothy	1
10		t3304067
11		Freddy	1
12		twest12
13	F Team	Nancy	1
14		d021300
15		Dina	2
16		llfinn
17		a3478u
18
Count

Cell Formulas
Range	Formula
C4:C18	C4	=IF(AND(EXACT(LEFT(B4,1),UPPER(LEFT(B4,1))),B4<>""),COUNTA(B5:B$100)-SUM(C5:C$100)-COUNT(C5:C$100),"")

 

[IOO]

A late hello & welcome to Mr. Excel, IOO

I was searching something & came across your post which is not answered yet. I don't know if you're still looking for a solution or not but I think the below code would achieve your goal without the need to RegEx

Sub CntKeys()

Dim j&

For x = Range("B" & Rows.Count).End(xlUp).Row To 4 Step -1
    If Left(Cells(x, 2), 1) Like "[A-Z]" Then
        Cells(x, 3) = j
        j = 0
    Else
        j = j + 1
    End If
Next

End Sub

Book1
	A	B	C
1
2
3	Group	Name	ID Count
4	Team A	Tracy	2
5		z12390
6		d76653
7	C Team	Richard	1
8		p56723
9		Timothy	1
10		t3304067
11		Freddy	1
12		twest12
13	F Team	Nancy	1
14		d021300
15		Dina	2
16		llfinn
17		a3478u
Sheet2

Wow! Worked like a charm. Could you please elaborate on the "4 step -1", I just want to understand the logic? Thanks!

 

[IOO]

.. or use standard worksheet functions. Just ensure where I have used $100 you use a row number that will definitely be as far as your last data.

20 05 10.xlsm
	A	B	C
1
2
3	Group	Name	ID Count
4	Team A	Tracy	2
5		z12390
6		d76653
7	C Team	Richard	1
8		p56723
9		Timothy	1
10		t3304067
11		Freddy	1
12		twest12
13	F Team	Nancy	1
14		d021300
15		Dina	2
16		llfinn
17		a3478u
18
Count

Cell Formulas
Range	Formula
C4:C18	C4	=IF(AND(EXACT(LEFT(B4,1),UPPER(LEFT(B4,1))),B4<>""),COUNTA(B5:B$100)-SUM(C5:C$100)-COUNT(C5:C$100),"")

Thanks for the response, this is good stuff.

 

[mse330]

Could you please elaborate on the "4 step -1", I just want to understand the logic? Thanks!

I'm looping from the last row with data in column B to row # 4 and the step -1 is to reduce the number in the loop by 1 since I am looping in reverse order (from 17 to 4)

Glad to help & thanks for the feedback

 

[Peter_SSs]

Thanks for the response, this is good stuff.

You're welcome. Thanks for the follow-up.

Also, just a slight variation to mse330's approach you may wish to consider if going down the macro path

Sub ID_Count()
 Dim x As Long, j As Long
 
 j = Range("B" & Rows.Count).End(xlUp).Row
 For x = j To 4 Step -1
    If Cells(x, 2).Value Like "[A-Z]*" Then
        Cells(x, 3).Value = j - x
        j = x - 1
    End If
  Next x
End Sub