VB Script to separate and sort data in Excel Workbook
Posted on 2012-08-29
I have an Excel workbook with 4 sheets. The sheets are named "AD UserGroups", "DBUsers", "Security Groups", and "Distribution Groups".
AD UserGroups contains information pulled from Active Directory in 2 columns. Column A is called Username and Column B is called Groups. There is 1 row for every Group that each Username is a member of. It looks like this:
DBUsers contains information pulled from an out of date user database in 2 columns. Column A is called EmpID and Column B is called Username. Each row contains a unique Employee ID number and the corresponding Username. It looks like this:
Security Groups contains a single column and each row lists a unique name for each Security Group in the database.
Distribution Groups contains a single column and each row lists a unique name for each Distribution Group in the database.
I am looking for someone to please help me with a VB script that will do the following:
I need the information in the "AD UserGroups" sheet to do the following checks against the other 3 sheets.
First I need each row of the "AD UserGroups" sheet to compare its username cell to the Usernames listed in the "DBUsers" sheet. If it does not find a matching username, I need need the row to be inserted into a new sheet called "UserNotFound".
If it does find a matching Username, then I need it to check the name of the group listed in the "Group" column against the "Security Groups" and "Distribution Groups" sheets. The name may exist in both sheets so both will need to be checked even if it is found in one. If the Group name is found, I need the row from "AD UserGroups" and the corresponding "EmpID" from "DB Users" to be inserted into one or both of 2 new sheets called "UserSecurity" and "UserDistribution".
Finally, if the group is not found in either of these sheets, the row from "AD UserGroups" and the corresponding "EmpID" from "DB Users" should be copied into a new sheet called "NoGroupFound".
I know this is a pretty complicated one (at least to me), so I really appreciate any help I can get.