VB Script to separate and sort data in Excel Workbook

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:
User1     Group1
User1     Group2
User2     Group1
User2     Group2
User2     Group9

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:

12345     User1
12346     User2
12458     User3

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.
Thank you.
Who is Participating?
I would go for another option:
if you have msexcel you probably have msaccess.
Set the columns names in excel in the first row (if not already there)

You can define a table as a link to a sheet in excel.
(steps :probably external data/excel/link (if have a msoffice 2007 dutch version))
You can do so for all 4 sheets.
In msaccess you have reporting facilities and there is a query wizard available that can cross-reference the defined tables
since i'm familiar with sql I define the queries in sql-syntax (available at a right click on the query)

the query for [User not found]
select * from [ad usergroups] where not exists (select 1 from dbusers where dbusers.username =  [ad usergroups].username)

the query for [User Security]
SELECT  [DBUsers].[empid], [AD Usergroups].[Username]
FROM [AD Usergroups],  [DBUsers], [Security Groups]
WHERE  [DBUsers].[username] =  [AD Usergroups].[Username]
AND  [AD Usergroups].[groups] = [Security Groups].[name]

for UserDistribution  change accordingly

and [no group found]
SELECT [DBUsers].[empid], [AD Usergroups].[Username]
FROM [AD Usergroups], DBUsers
WHERE [DBUsers].[username] =  [AD Usergroups].[Username]
AND NOT EXISTS (SELECT 1 FROM  [Security Groups] WHERE   [AD Usergroups].[groups] = [Security Groups].[name])
AND NOT EXISTS (SELECT 1 FROM  [Distribution Groups] WHERE   [AD Usergroups].[groups] = [Distribution Groups].[name]);

You can change the excel data afterwards , the link in msaccess will still exist and you can requery the queries.
HLR6SAuthor Commented:
Thank you, that alternative solution worked great.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.