Compare Excel Data

[Country73]

I'm currently looking at writing a script to handle the task of comparing data from two separate Excel files, then writing the data to a third Excel file.

I've done some similar work like this in the past, using VBScript, but curious as to the best way I can handle this through AutoIT.

This project is to compare an Excel file that stores accounts that are setup as Local Admins across the network (from running a scan), and an Excel file that was manually created to create accounts that are setup as Local Admins on a specific workstation with a reason for the setup.

In a nutshell, need to determine which accounts are documented with a reason for having Local Admin rights, and which accounts need to be removed. - Audit purpose.

Excel_1 = MASTER - Manually created file containing account to machine and reason for Local Admin rights.

Excel_2 = TARGET - Automatically created by scanning the network and extracting accounts currently setup as Local Admin.

Excel_3 = DELETE - Accounts setup as Local Admin, but do not exist in the 'MASTER' list.

MASTER layout.

Column A = Machine Name

Column B = Reason for Local Admin Rights

Column C = Account Name *this may also include columns D - ?? depending on how many accounts are setup

TARGET layout.

Column A = Machine Name

Column B = Account Name *this may also include columns C - ?? depending on how many accounts are setup

DELETE layout.

Column A = Machine Name

Column B = Account to Delete *this may also include columns C - ??

I'm trying to determine the best way to write this script. Best as in the quickest way to compare TARGET and MASTER then record to DELETE.

Curious on best route to take:

EXAMPLE 1 - ARRAY:

Mach1 is listed in MASTER with one account. - one account recorded as requiring Local Admin.

Mach1 is listed in TARGET with five accounts. - five accounts currently setup as Local Admin on machine.

TARGET accounts are added into an ARRAY and MASTER account compared to the ARRAY.

All NON-Matched accounts are recorded into DELETE.

EXAMPLE 2 - Cell by Cell:

Mach1 is listed in MASTER with one account. - one account recorded as requiring Local Admin.

Mach1 is listed in TARGET with five accounts. - five accounts currently setup as Local Admin on machine.

Compare the 'MASTER-account' against all five of the 'TARGET-accounts', cell by cell, and record all NON-Matching accounts into DELETE.

Suggestions?

Thanks,

*Edit -

EXAMPLE 1 and 2, there are possibilities of more than one account listed in the MASTER file.

Edited January 22, 2009 by Country73

[Zedna]

Look at Excel UDF. It's in AutoIt v 3.3 as standard include file.

[Country73]

Look at Excel UDF. It's in AutoIt v 3.3 as standard include file.

Thanks for the reply,

I'm using the ExcelCOM_UDF, but curious as to the best route to take.

I know what needs to be compared and I just finished setting up a compare of Cell-to-Cell.

I just don't want to spend too much time trying to work in a different route (possibly Arrays) if it's not going to be of any real benefit.

My current test doesn't seem to take too long to run through, but it'll be a different story when I'm comparing all the entries for over 2000 workstations.

Just looking for any suggestions right now.

Thanks,