VB Script to separate and sort data in Excel Workbook

Posted on 2012-08-29
Medium Priority
Last Modified: 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:
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.
Question by:HLR6S
LVL 21

Accepted Solution

flow01 earned 2000 total points
ID: 38346167
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.

Author Closing Comment

ID: 38347269
Thank you, that alternative solution worked great.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question