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
Display More