Help with count of rows (1 Viewer)

lookforsmt

Registered User.
Local time
Tomorrow, 02:56
Joined
Dec 26, 2011
Messages
672
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.
Code:
Sub FieldAC()
    On Error Resume Next
    Dim nc1 As New Collection, nc2 As New Collection, nc3 As New Collection, nc4 As New Collection
    Dim r As Range, cel As Range
    Dim wsMain As Worksheet, wsEMI As Worksheet
    Dim x As Integer
    Set wsMain = Sheets("Summary")
    Set wsEMI = Sheets("X_Report")
    Set r = wsEMI.Range(wsEMI.Range("D12"), wsEMI.Range("E" & Rows.Count).End(xlUp))
        For Each cel In r
            nc1.Add cel.Offset(, -1), cel
            nc2.Add cel, cel
            nc3.Add cel.Offset(, 1), cel
            nc4.Add cel.Offset(, 2), cel
        Next cel
    For x = 0 To nc1.Count
        wsMain.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = nc1(x)
        wsMain.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = nc2(x)
        wsMain.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = nc3(x)
        wsMain.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = nc4(x)
    Next x
    MsgBox "Done"
End Sub
 

lookforsmt

Registered User.
Local time
Tomorrow, 02:56
Joined
Dec 26, 2011
Messages
672
attaching the excel file
 

Attachments

  • ReconTest_v2.zip
    29.9 KB · Views: 562

Darrell

Registered User.
Local time
Today, 22:56
Joined
Feb 1, 2001
Messages
299
How about having this formula in the count column of your summary sheet or am I missing something..?

=COUNTIF(X_Report!$E$12:$E$68,Summary!B2)
 

Users who are viewing this thread

Top Bottom