VB Script to separate and sort data in Excel Workbook

Posted on 2012-08-29
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 20

    Accepted Solution

    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

    Thank you, that alternative solution worked great.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now