Comparing a list of Records

[ttp]

I need assistance comparing a list of records. Within my file, there are
records with multiple records. I want to perform the following If
statements. But, I don't know how to arrange it in Excel or Access.

If all the items in a field (column) begin with CR,
then variable = "HQ"
If all the items in a field (column) begin with DR,
then variable = 'OC"
Else variable = "N/A"

 

[Code Cage Team]

What is it you want to do with this variable?, do you want each record
marking with the variable or do you want the records moving to a sheet with
the variables name?, you need to give us a little more to go on.

 

[JLGWhiz]

Assume the column to search is Column A with headers in Row 1.

Dim lr As Long, c As Range, iCnt As Long, jCnt As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set sRng = ActiveSheet.Range("A2:A" & lr) 'Change to actual
rCnt = sRng.Rows.Count
For Each c In sRng
If Left(Trim(c.Value),2) = CR Then
iCnt = iCnt + 1
ElseIf Left(Trim(c.Value, 2) = DR Then
jCnt = jCnt + 1
End if
Next
If rCnt = iCnt Then
myVar = HQ
ElseIf rCnt = jCnt Then
myVar = OC
Else
myVar = N/A
End If

This code is based on what you described in your
posting. While I doubt that it is really what you are
looking for. It will do what you described.

 

[JLGWhiz]

Forgot the quotation marks.


Dim lr As Long, c As Range, iCnt As Long, jCnt As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set sRng = ActiveSheet.Range("A2:A" & lr) 'Change to actual
rCnt = sRng.Rows.Count
For Each c In sRng
If Left(Trim(c.Value),2) = "CR" Then
iCnt = iCnt + 1
ElseIf Left(Trim(c.Value, 2) = "DR" Then
jCnt = jCnt + 1
End if
Next
If rCnt = iCnt Then
myVar = "HQ"
ElseIf rCnt = jCnt Then
myVar = "OC"
Else
myVar = "N/A"
End If