Parsing and Counting Matches - My State Table.xls (0/1)

  • Thread starter Thread starter rparrish
  • Start date Start date
R

rparrish

I have two columns of data. The first column is not important for
this exercise but the second column contains a series of state
initials. I need to count the number of times the states initials
appear in the second column and place the total count in a column
designated for that state. I have attached a two row sample of what
the finished sheet should look like. I am not a VBA programmer but
have a lot of programming experience in other programming languages.
Can this be accomplished using a series of Excel functions or do I
need to write a VBA program to do this? Any help with this is greatly
appreciated.

Russ Parrish
(e-mail address removed)
 
I have two columns of data. The first column is not important for
this exercise but the second column contains a series of state
initials. I need to count the number of times the states initials
appear in the second column and place the total count in a column
designated for that state. I have attached a two row sample of what
the finished sheet should look like. I am not a VBA programmer but
have a lot of programming experience in other programming languages.
Can this be accomplished using a series of Excel functions or do I
need to write a VBA program to do this? Any help with this is greatly
appreciated.

Russ Parrish
(e-mail address removed)
Click to expand...
 
Congratulations! You have just figured out how to alienate many of us on
this ng by attaching a file. Not only that, you did it three times.

Despite this, someone will probably reward you with an answer.
 
No attachments in CDO!

Biff
-----Original Message-----
Congratulations! You have just figured out how to alienate many of us on
this ng by attaching a file. Not only that, you did it three times.

Despite this, someone will probably reward you with an answer.

--
Don Guillett
SalesAid Software
(e-mail address removed)



.
Click to expand...
 
Hi!

I can't see your attachment but try tinkering along these
lines:

=COUNTIF(B2:B100,"PA")

PA = Pennsylvania

If "PA" is held in a cell like D5:

=COUNTIF(B2:B100,D5)

Biff
 
Thanks Biff. I tried to attach my file but I obviously don't know how
to do that correctly. The Cells which contain the State Abbreviations
contain several in the form "VA, VA, NC, CA, IA, IA." and I need to
count how many time each State Abbreviation occurs in each cell. The
counts are then entered into separate columns with the State
Abbreviations as headers. The example below is what I am looking for.

NC VA CA IA MO
NC, NC, VA, CA 2 1 1
MO, IA, VA, CA 1 1 1 1
Total 2 2 2 1 1
 
I started constructing a little VBA routine to do this for me. It's
just starting to take shape and I have a looping error I have to take
care of but its beginning to do exactly what I whated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Search for occurance in a Cell of a string found in a specific Cell
' Two letters found in Columns 3 - 10 will be used as the String to
Search for
' in the next cell in the 2nd column. When a match is found, increment
the match counter
' When the end of the the cell is encountered, advance to the next cell.
' Loop will continue until search cell is blank.
' At the end of each search cell put match counter in approprirate row
and column
' Need nested loops to traverse both the Search Cell and the String Cells.

' Define variables

Dim CellString As String 'Cell to search
Dim SearchString As String ' String search for
Dim I As Integer

Dim CellEnd As Integer ' End of Search Cell
Dim CharCnt As Integer ' Character Position in Search Cell
Dim CellRow As Integer ' Row for Search Cell, also used for inner loop
control and row to write resluts
Dim CellCol As Integer ' Column for Search Cell
Dim SearchCol As Integer ' Cell Coloumn for String to Search for, also
for outer loop control and column to write results
Dim SearchRow As Integer ' Cell Row for string to search for
Dim MatchCnt As Integer ' Number of times the Search string is found
in the search cell.
Dim Returncode As Integer

'Initialize variables

CharCnt = 1 'first character of cell to look for match
CellRow = 2 ' Initialize Cell Row
CellCol = 2 'Initialize Cell Column B
SearchRow = 1 'Initialize Search String Row
SearchCol = 3 'Initialize Search String Column C
MatchCnt = 0 ' Initialize Match counter
Returncode = 1 ' varible to capture the position where match was found

' Outer loop controls the column used to get the search string
' Inner loop controls which cell to search for matches

Do Until Len(Cells(CellRow, CellCol)) <= 0
SearchString = "Start"
Do While Len(SearchString) > 0
SearchString = Cells(SearchRow, SearchCol) ' Get the string to
search for
CellString = Cells(CellRow, CellCol) 'get the search string
MsgBox (SearchString)
MsgBox (CellString)
If Len(CellString) <= 0 Then
End
End If
Returncode = 1
Do While Returncode > 0
MsgBox (CharCnt)
Returncode = InStr(CharCnt, CellString, SearchString, vbTextCompare)
If Returncode > 0 Then
MatchCnt = MatchCnt + 1
CharCnt = Returncode + 1
Else
Cells(CellRow, SearchCol).Value = MatchCnt
MsgBox (MatchCnt)
End If
Loop
' Need to get next Search String and reset CellString pointer and
clear matchcnt.
CharCnt = 1
SearchCol = SearchCol + 1
Loop
CellRow = CellRow + 1
Loop

End Sub
 
Here is the finished product. Its a simple VBA routine that will work
with any data and is not dependent on the size of the text strings. The
only hardcoded values I used, and I will replace with a prompt, is the
starting cells for both the search strings and the cells on which to
perform the search. I used the solution Biff developed for my work
requirement because I wanted to use native Excel rather than maintain
custom code. Our office performs a lot of data collection for monthly
performace stats and often the volumes of data we get are in the
thousands of rows and routines like this can be a big time saver.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Search for occurance in a Cell of a string found in a specific Cell
' Two letters found in Columns 3 - 10 will be used as the String to
Search for
' in the next cell in the 2nd column. When a match is found, increment
the match counter
' When the end of the the cell is encountered, advance to the next cell.
' Loop will continue until search cell is blank.
' At the end of each search cell put match counter in approprirate row
and column
' Need nested loops to traverse both the Search Cell and the String Cells.

' Define variables

Dim CellString As String 'Cell to search
Dim SearchString As String ' String search for
Dim I As Integer

Dim CharCnt As Integer ' Character Position in Search Cell
Dim CellRow As Integer ' Row for Search Cell, also used for inner loop
control and row to write resluts
Dim CellCol As Integer ' Column for Search Cell
Dim SearchCol As Integer ' Cell Coloumn for String to Search for, also
for outer loop control and column to write results
Dim SearchRow As Integer ' Cell Row for string to search for
Dim MatchCnt As Integer ' Number of times the Search string is found
in the search cell.
Dim Returncode As Integer

'Initialize variables

CharCnt = 1 'first character of cell to look for match
CellRow = 2 ' Initialize Cell Row
CellCol = 2 'Initialize Cell Column B
SearchRow = 1 'Initialize Search String Row
SearchCol = 3 'Initialize Search String Column C
MatchCnt = 0 ' Initialize Match counter
Returncode = 1 ' varible to capture the position where match was found

' Outer loop controls the column used to get the search string
' Inner loop controls which cell to search for matches

Do While Len(Cells(CellRow, CellCol).Value) > 0
SearchString = Cells(SearchRow, SearchCol) ' Get the string to
search for
CellString = Cells(CellRow, CellCol) 'get the search string
Do While Len(SearchString) > 0
Returncode = 1
Do Until Returncode = 0
Returncode = InStr(CharCnt, CellString, SearchString, vbTextCompare)
If Returncode > 0 Then
MatchCnt = MatchCnt + 1
CharCnt = Returncode + 1
Else
Cells(CellRow, SearchCol).Value = MatchCnt
End If
Loop
' Need to get next Search String and reset CellString pointer and
clear matchcnt.
CharCnt = 1
MatchCnt = 0
SearchCol = SearchCol + 1
SearchString = Cells(SearchRow, SearchCol)
Returncode = 0
Loop
CellRow = CellRow + 1
SearchCol = 3
Loop
End Sub
 
Back
Top