Help with count of row for unique records

  • HI all

    i am working on automating the recon process which is coming out from the source file. It with three tabs
    a) DataSource
    b) Summary
    c) Recon

    DataSource is the actual data for a particular date with 40+ columns

    a) I am able to get code for unique Names based on the account in column (D; E; F; G) and copy the unique cells to the Summary tab.
    What i am looking is how to get the count of these unique records by AC nos in column E

    Below vba code, need help to get this worked with count.
    I am attaching my excel for better clarity.

  • Hello and Welcome to the Forum :)


    Not sure to understand exactly ...what do you need to count ...and where should the count appear ...


    You have a code dealing with Collections ... the instruction nc1.Count does provide the Count ... so again could you be more precise ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you Carim for taking time to read my thread.

    Appologies for the incomplete information.

    firstly, i am very weak in vba, but i good at looking at someone's work and make it work for myself. I tried high and low on the net but failed to get anything close.

    When i run the macro FieldAC, it will copy unique records in column A to D in Summary tab. I am looking to sum the count of these records and update in column E. I have put a actual count in summary tab as an example.

    hope i am clear.

  • Thanks for your explanation ...


    Many different solutions ...

    1. If you need a formula ... in cell E2 you can have

    Code
    1. =COUNTIF(X_Report!$D$12:$D$68,A2)

    and copy it down ...


    2. The same count could be integrated in the macro ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • No problem ...;)


    see modification below :


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the macro ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Apologies for the delay,

    Thank you Carim for the code, i have adjusted it to my project and its working well. Thanks once again.


    just one last help in the below code (full code is in post #6 pls

    Code
    1. wsMain.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = nc4(x)

    In sheet "Summary", under column D, want to get values only where the Cus Seg is "CR-CAT" in the below code line. The code can ignore the other items.

    Is this possible to modify the code mentioned in post #6

  • Glad to hear you have fixed your problem :)


    Thanks for your Thanks ...AND for the Like :thumbup:


    Will into your latest question ... as soon as I have a moment ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Had a quick look at your file ...


    A couple of remarks :


    1. Standard macros should be stored in Standard Modules ...( Not in the Sheet Module ...)


    2. Regarding your latest question, do you need a Filter ... please update the test file to show the expected final result you are looking for


    Hope attached test file will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • HI Carim, i have checked the attached excel, there is no change from the last excel. Sorry if i have missed something pls.


    Well as shown on your picture ...CR-CAT do appear in yellow ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • apologies again for not being clear, i want only the yellow highlighted without the others rows


    Did you read my message # 10 ...


    Quote

    2. Regarding your latest question, do you need a Filter ... please update the test file to show the expected final result you are looking for

    You've decided ... not to answer ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • No sir, i did read your post# 10, what i understood from it is

    a) to keep all codes in module and not in sheet

    b) you asked if i need a filter and you have attached excel by name Test maheshtV2 where the code is moved in module.


    I did reply in post #11 acknowledging about the first part.

    for the 2nd part, i only wanted yellow highlighted details without other rows.


    I am trying to just clarify my point.

  • In order to Filter, you need a new macro


    Code
    1. Sub Filter_CR_CAT()
    2. Dim wsMain As Worksheet, wsEMI As Worksheet
    3. Dim last As Long
    4. Set wsMain = Sheets("Summary")
    5. Set wsEMI = Sheets("X_Report")
    6. last = wsEMI.Cells(Rows.Count, "D").End(xlUp).Row
    7. wsEMI.Range("D11:G" & last).AutoFilter Field:=4, Criteria1:="CR-CAT"
    8. wsEMI.Range("D12", wsEMI.Range("G" & Rows.Count).End(xlUp)).SpecialCells(12).Copy wsMain.Range("A2")
    9. wsEMI.Range("D11:G" & last).AutoFilter Field:=4
    10. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)