Count column if NOT a duplicate (Excel 2010)

[lawitcher1]

Accidentally posted twice, see next post please

[lawitcher1]

I made (I thought) the changes that you referred to, but still not working properly. Here is what I have
[color=#FF0040][color=#FF0040]

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim rng As Range
   
'limit cell monitoring
If Target.Row < 7 Or Target.Count <> 1 Then Exit Sub

'columns to check
If Target.Column <> 1 And Target.Column <> 11 And Target.Column <> 12 And Target.Column <> 13 Then Exit Sub

'establish last row to check for duplicate
LastRow = Cells(Rows.Count, Target.Column).End(xlUp).Row

'prevent this event from calling itself
Application.EnableEvents = False


'checking col A         '<~~~~~~ column 1
If Target.Column = 1 Then
    'rng of concern
    Set rng = Range("A7:A" & LastRow)
    'if target is not empty
    If Target.Value <> "" Then
    'count how many times target appears in rng
        If Application.WorksheetFunction.CountIf(rng, Target.Value) = 1 Then
            'it's targets first occurrance, put date/time into adjacent cell
            Target.Offset(0, 1).Value = Now + 8 / 24
            'remove any existing cell fill color
            Target.Interior.ColorIndex = 0
        Else
            'it's a duplicate
            'put date/time in adjacent cell
            Target.Offset(0, 1).Value = Now + 8 / 24
            'indicate it's a duplicate
            Range("A3").Value = Target.Value
            'color the just entered duplicates cell
            Target.Interior.ColorIndex = 44
        End If
    'if target is empty ie:been deleted
    Else
        'remove date/time
        Target.Offset(0, 1).Value = ""
        'clear duplicate indication
        Range("A3").Value = ""
        'remove cell color
        Target.Interior.ColorIndex = 0
    End If

'checking col K         '<~~~~~~ column 11
ElseIf Target.Column = 11 Then
    'rng of concern
    Set rng = Range("K7:K" & LastRow)
    'do what you want for column K
    'count how many times target appears in rng
    If Application.WorksheetFunction.CountIf(rng, Target.Value) = 1 Then
        'it's first not duplicate
        'clear anything in K3
        Range("K3").Value = ""
        'reset target color
        Target.Interior.Color = RGB(217, 217, 217)
    Else
        'indicate it's a duplicate
        Range("K3").Value = Target.Value
        'color the just entered duplicate cell
        Target.Interior.ColorIndex = 44
            End If

'checking col L         "<~~~~~~ column 12
ElseIf Target.Column = 12 Then
    'rng of concern
    Set rng = Range("L7:L" & LastRow)
    'count how many times target appears in rng
    If Application.WorksheetFunction.CountIf(rng, Target.Value) = 1 Then
        'it's first not duplicate
        'clear anything in L3
        Range("N3").Value = ""
        'reset target color
        Target.Interior.Color = RGB(217, 217, 217)
    Else
        'indicate it's a duplicate
        Range("L3").Value = Target.Value
        'color the just entered duplicate cell
        Target.Interior.ColorIndex = 44
       
 'checking col M         "<~~~~~~ column 13
ElseIf Target.Column = 13 Then
    'rng of concern
    Set rng = Range("M7:M" & LastRow)
    'count how many times target appears in rng
    If Application.WorksheetFunction.CountIf(rng, Target.Value) = 1 Then
        'it's first not duplicate
        'clear anything in M3
        Range("M3").Value = ""
        'reset target color
        Target.Interior.Color = RGB(217, 217, 217)
    Else
        'indicate it's a duplicate
        Range("M3").Value = Target.Value
        'color the just entered duplicate cell
        Target.Interior.ColorIndex = 44
    End If
       
       
End If

'make sure events are re-enabled
Application.EnableEvents = True

End Sub

[/color][/color]

[NoSparks]

Well, this is the response I had to the post you've removed.

Looks good to me except for two things:
Two Target.Column 12 s under 'columns to check and
That isn't the code in the file you posted. Wrong file maybe?

******************************************************************************

For what you've posted now,
That should work, provided you add an End If at the end of the 'checking col L section same as there is at the end of the K section.

You should also edit your last post before Pecoflyer gives the lecture about code tags.
Highlight all the code, the click the Code button to add code tags.

[lawitcher1]

Thank you for the "End If" that I missed in the formula. Everything appears to be working now. Also thank you for the posting tip. I think that I have re-posted it correctly. If I have not highlighted and made a code input around the code in the proper manner, please let me know and I will go back and try to redo it properly.

Now for the duplication in QSO's (contacts) in column "A". In the attached, Column A, lines 7-10 should equal a QSO (contact) count of 4. Even though the name and same band is duplicated, the mode is different. A QSO (contact) can be made on each of the six designated frequencies (160m, 80m, 40m, 20m, 15m and 10m) (see the dropdown box in column C) on Phone and again on CW. so theoretically, there could be 12 QSO's for the same name (6 on phone for each designated frequency) plus (6 on CW for each designated frequency) for a total of 12 QSO's for the same call sign (name).This will in no way change anything in column K (Country Worked), Column L (State Worked) and Column M (County worked), their parameters will remain the same. It would also be nice if a different color of a duplicate call sign (name) per the above rules could be added, and if it is a true duplicate (not meeting the above standards) the color will stay as is. Also, is it possible for the Call name to NOT appear in cell "A3" unless it is indeed a TRUE DUPLICATE? Please see attached file for reference.

[NoSparks]

Well.... assuming the info is entered in order, left to right along the rows, the true duplicate wouldn't be known until something is entered in column E, so that's when the check for "real" duplicates gets initiated.
I think the attached file does what you're after.

I'm afraid a formula for A5 is beyond my Excel knowledge. If you're really wanting a formula, and you're probably best to have one, you may need to break this part out of the project and ask it as a separate question on the formulas side.

I do have some code in there that calculates what I think should (maybe?) be the result in A5.
Temporarily displaying it in A4. You'll definitely have to verify it.
Hope things are commented enough to figure out what's happening.

Good Luck with the project
NoSparks

[lawitcher1]

Thank you very, very much for your help, and most importantly the lessons learned. We are so close. I will take your advice and repost under a different question.

[NoSparks]

Larry,

From reading your other post, can you varify if I have this figured right

for #1)

• 1 contact, 2 possible modes, 6 possible bands --> possible count of 12 per contact

• nothing to the right of column E has any bearing on this

for #2)

• each PH counts as 2, each CW counts as 3 towards value in E5

• if contact is a duplicate as determined by #1, that rows PH or CW is not counted

for #3)

• duplicates in column L are not based on anything else in column L

• duplication is exactly the same as column A

• each non duplicate counts 1 towards value in L5

for #4)

• duplicates in column M are not based on anything else in column M

• duplication is exactly the same as column A

• each non duplicate counts 1 towards value in M5

[lawitcher1]

• 1 contact, 2 possible modes, 6 possible bands --> possible count of 12 per contact

PARTIALLY CORRECT - 1 contact (Column A) for: 2 possible modes (Column E), on each Band ( 6 in column C), each State or Canadian Province (58 in column L), or each County (58 in Column M) Cell A5 counts every contact, even the duplicates. Cell A4 counts only the non-duplicates.

• nothing to the right of column E has any bearing on this for #2)

WRONG. See Bullet #1

• each PH counts as 2, each CW counts as 3 towards value in E5

CORRECT, if bullet # 1 is followed.

• if contact is a duplicate as determined by #1, that rows PH or CW is not counted for #3)

CORRECT, if it follows the restrictions of bullet #1.

• duplicates in column L are not based on anything else in column L

WRONG, See Bullet #1

• duplication is exactly the same as column A

WRONG, See bullet #1

• each non duplicate counts 1 towards value in L5 for #4)

CORRECT if bullet #1 is followed. Columns E, L & M are not counted if a true duplicate. Cell A5 counts every contact, even the duplicates. Cell A4 counts only the non-duplicates.

• duplicates in column M are not based on anything else in column M

CORRECT (again see bullet #1)

• duplication is exactly the same as column A

PARTIALLY CORRECT, if the guideline in bullet #1 are followed, this is true.

• each non duplicate counts 1 towards value in M5

CORRECT as do the values in columns E & L if the guidelines of bullet #1 are followed.

If I have understood everything correctly, the above is correct. Here is a recap of the rules for this contest.


You get 3 points for each valid CW contact, and 2 points for each valid Phone contact. You can work a station once per band and once per mode.

Out of CA stations only get credit for working CA stations.
CA stations get credit for working anyone - hence, DX contacts only count for CA stations.

There are 58 multiples possible, no matter where you are located. 50 states & * Canadina Provinces, including California (for inside CA stations), and 58 counties (for out of CA stations).

For the contact to be valid, you must log all the exchange info correctly, including the county, even if you are a CA station.

So, if you worked 10 CW stations, and 10 phone stations, each as a new multiple, then your score would be > (10x3 + 10x2) x 20 = 1,000 pts.

[NoSparks]

With regards to the rule:
For the contact to be valid, you must log all the exchange info correctly, including the county, even if you are a CA station.

Can you define "all the exchange info" as this will be the first requirement for any contact to even be considered a contact.
No contact, no nothing.

[lawitcher1]

I agree. I still don't know how to compose the rule to reflect the changes needed.