Comparing two excel Sheets whose columns names vary

[Parke]

Divya:

I assume that the columns for the two excels are in a different order e.g.
excel_1 has columns id,first,last,isMarried,numChildren and
excel_2 has columns last,numChildren,id,first,isMarried

I assume the two spreadsheets will have the same number of rows. (different number of rows can be handled but the programming gets messy)

I assume e.g. the data in row1 in excel_1 might be in row22 in excel_2.

Yes, it is possible to write a script to compare the two spreadsheets. This will involve using dictionaries.

Create two dictionaries, dict_1 and dict_2. Find one or two columns that will create a unique key. In my case above, it would be the id.

Code:

key_1 = sheetName_1.cells(i,1).value

All the other columns, first, last, isMarried, numChildren will make up the value

Code:

value_1 = sheetName_1.cells(i,2).value & ";:" & sheetName_1.cells(i,3).value &";:"& sheetName_1.cells(i,4).value & ";:" & sheetName_1.cells(i,5).value

For dict_2:

Code:

key_2 = sheetName_2.cells(i,3).value
value_2 = sheetName_2.cells(i,4).value & ";:" & sheetName_2.cells(i,1).value &";:"& sheetName_2.cells(i,5).value & ";:" & sheetName_2.cells(i,2).value

Note the value in both of these are "first, last, isMarried, numChildren"

So do a loop and create both dictionaries. I like using the dictionaries that are in dotnetfactory.
I use dotnetfactory as I find it easier to use the Enumerator then throwing the keys into an array.

Code:

Set dict_1 = dotnetfactory.CreateInstance("System.Collections.Specialized.ListDictionary")
Set myEnum_1 = DotnetFactory.CreateInstance("System.Collections.IDictionaryEnumerator")

Set xLApp_1 = CreateObject("Excel.Application")
xlApp_1.visible = True
Set xlWB_1 = xlApp_1.workbooks.Open
Set sheetName_1 = xlWB_1.Sheets("Sheet1")
nrows = sheetName_1.usedrange.rows.count
ncolumns = sheetName_1.usedrange.columns.count

go through a for/next loop and fill in dict_1.

Do the same for dict_2.

Now comes the time to check the values of the dictionaries.

Code:

Set myEnum_1 = dict_1.GetEnumerator
Set myEnum_2 = dict_2.GetEnumerator
rownum = 1

This is where things get a bit messy since the rows are not the same in the two spreadsheets. Grab the key in dict_1, loop through dict_2 to find the same key and compare dict_1 value with dict_2 value. If they are different, change the cell color e.g.

Code:

xlApp_1.cells(rownum,1).Interiow.colorIndex = 3

.

You can go further and find which cell is different by doing splits and comparing the values of the arrays and then further changing that one cell.

I hope this helps and I have not caused mass-confusion. I have written code to compare two spreadsheets when the columns and rows are identical and this method of using dictionaries is slightly faster than using the functionality that is build into QTP .