VBA code to print unique values and corresponding values from the other coloumn

[RahulA1]

Hi Members

I am stuck at a situation. I have a excel sheet with two columns. Column A has Departments and Column B has corresponding "employees". This sheet is named as IN sheet in all the excel files. There are only 2-3 departments but for each department there are multiple Employee names. I am writing a code and I need help with looking for a sheet named "IN" or "IN*" in the given workbook and look for column Department and print the unique values in these departments and the corresponding number of entries in Employee Column.


Sheet format with two columns
DEPT EMPLOYEES
ZZZ ABC
ZZZ ASD
ZZZ BVC
YYY XYZ
YYY AAA
YYY XCV
YYY XRT

Result Expected: (any Cell is OK)

ZZZ 3
YYY 4


Any help will be of great help. Thanks

[mana]

Option Explicit


Sub test()
Dim ws As Worksheet
Dim r As Range, dst As Range

For Each ws In Worksheets
If ws.Name Like "In*" Then
Set r = ws.Columns("C:D")
Set dst = r.CurrentRegion.Cells(1).Offset(, r.CurrentRegion.Columns.Count + 2)
dst.Consolidate r.Address(, , xlR1C1), xlCount, True, True
End If
Next

End Sub



マナ