i am working on automating the recon process which is coming out from the source file. It with three tabs
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.
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