Vba to count occurrence if it appears more than 3 times

  • Hello,


    need urgent assistance please


    in the attached workbook column B contains unique Identifies. I want to be able to run a code to count all identifiers that have been entered 3 times or more ie count all identifiers and how many times that particular identifier is in column B


    I kinda need this urgently and would really appreciate any help!


    the attached file is a test file as the original contains 2000 plus identifiers that will need to be counted .


    Thanks

  • Re: Vba to count occurrence if it appears more than 3 times


    sorry to be a pain, any chance someone could possibly have a look at a possible solution.


    I have a time scale to have this done by and its running out.


    Thanks again guys

  • Re: Vba to count occurrence if it appears more than 3 times


    Hi,


    It's a little confusing, as you say that column B contains unique identifiers, but then say that you want to count how many times a particular identifier appears in column B. If they were unique then the answer would be 1.


    Do you actually need VBA to do this? It can be done relatively simply with standard Excel functionality.


    Use Advanced Filter to create a list of the unique entries in column B (use 'copy to another location' elsewhere in the worksheet), and then against each item in the new list place a COUNTIF formula. I have set the destination for the Advanced Filter as $D$1, so that the unique list starts at D2, in which case the formula, filled down to the end of the new range, would be:


    =COUNTIF($B$2:$B$36,D2)>=3


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Vba to count occurrence if it appears more than 3 times


    Thanks for the reply. The identifiers are unique (but appear more than once as that particular identifies is affiliated to a service user which may have attended more than 1 session ie attended Stay & Play and Creche.
    I did think about your solution but as there are thousands of attendances by thousands of service users that would make it difficult to count hence why I thought vba would be the best solution.

  • Re: Vba to count occurrence if it appears more than 3 times


    thanks for the reply.


    im getting the below error when trying to open your attachment,


    Invalid Attachment specified. If you followed a valid link, please notify the administrator

  • Re: Vba to count occurrence if it appears more than 3 times


    what id like to be able to do is:


    [TABLE="width: 128"]




    [tr]


    [TD="width: 64, bgcolor: transparent"]Identifier[/TD]


    [TD="width: 64, bgcolor: transparent"]Count (x3 or more[/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"]A408028[/TD]


    [TD="bgcolor: transparent, align: right"]5[/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"]B403352[/TD]


    [TD="bgcolor: transparent, align: right"]6[/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"]D408824[/TD]


    [TD="bgcolor: transparent, align: right"]10[/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"]D408825[/TD]


    [TD="bgcolor: transparent, align: right"]15[/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"]H405268[/TD]


    [TD="bgcolor: transparent, align: right"]6[/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"]J410230[/TD]


    [TD="bgcolor: transparent, align: right"]4[/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"]K413838[/TD]


    [TD="bgcolor: transparent, align: right"]6[/TD]


    [/tr]



    [/TABLE]

  • Re: Vba to count occurrence if it appears more than 3 times


    hi, I managed to open the file. th pivot table looks good but the only real issue is I need to only count those identifiers who appear 3 times or more,

  • Re: Vba to count occurrence if it appears more than 3 times


    Here is a VBA code you can use to do what you want, it will list all identifiers with count of 3 or more on sheet 2, you can manipulate as you need


    Add this code to have the code run anytime the values in column B change

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = yes Then
        Call UniqueIdentifiers
    End If
    End Sub



  • Re: Vba to count occurrence if it appears more than 3 times


    For future reference, please take the time to read the board rules, in particular #4.


    [cp]*[/cp]


    Links should be added when you first post, or after posting on another site, not as a comment when you get a solution. You should also be aware other boards usually ask the same, as well.

  • Re: Vba to count occurrence if it appears more than 3 times


    Hi,
    Following on from my earlier query which was resolved


    I now have another based upon my original post but requiring further filtering.


    I require data to be filtered with the below conditions:


    Identifier who has attended 3 or more sessions (this part works with the code supplied)


    The next step is:
    I would than like to filter these results based on columns D & E ie
    Once I have ascertained the No of Identifiers who have attended 3 or more session it like to be able to see how many of them were 0Yy, 1Yr, 2Yr,3Yr this data is in column D and lastly based on the these results id than like to be able to filter based on my selection from column E


    So in summary of what im trying to do.


    Show all identifiers who have attended 3 or more sessions and who are 1 and who have a score of 3


    I hope the above is not too confusing lol


    Appreciate all help!

  • Re: Vba to count occurrence if it appears more than 3 times


    Do you want the code to just filter the data on sheet1 as per requirements or to put the results of the filtering on sheet2?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Vba to count occurrence if it appears more than 3 times


    hi,


    I would like it to place the data in sheet 2 please. however if you have a better solution id be happy for you to go with that.


    Thanks

  • Re: Vba to count occurrence if it appears more than 3 times


    post 16 were I mentioned:


    Show all identifiers who have attended 3 or more sessions and who are 1 and who have a score of 3


    please note the second & third criteria are subject to change depending on requirements so the possible selections for column D could be 0,1,2 or 3 and similarly column E are 1,2 or 3


    apologies if I wasn't clear in post 16

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!