Comparing two excel Sheets whose columns names vary

[Divya Roopa]

Hi Sir/mam,
I have a scenario where i need to compare my data which is in External Excel sheet(Excel1) with Database Table.Here i would take all the values of database into another Excel sheet(Excel2).Now i need to compare both the Excel sheets whose columns name vary and Data in that is randomly arranged column wise.can anyone help me out.How to generate a script for this?
Thanks in Advance

[Parke]

Divya:

I can outline a procedure that I believe will work. It involves using dictionaries.

Is there one or a combination of two columns that are unique? A unique value is required to be the key of a dictionary.(Sorry, I do not know if you are familiar with dictionaries. I also like to use the dictionaries in DotNetFactory.)
I am going name the first excel as x, and the second excel as y.
I will assume that in x, column 3 is the same as column 2 in y and the values in these columns are unique. (xcolumn3 holds the same data as ycolumn2, just in a different order.) We will use two dictionaries, dict_x and dict_y.

Code:

pseudocode
keyx = sheetName_x.cells(i,3).value

The values will be everything else.
valx =  sheetName_x.cells(i,1).value &";:"&sheetName_x.cells(i,2).value &";:"&sheetName.cells(i,4).value, etc.
So the dictionary can have key/value added to it.
dict_x.add keyx, valx

Where you will have "fun" is with dict_y.  The dictionary values (columns) need to be in the same order as you used in dict_x.

If excel x columns are (first, last, id, married, numchildren) and if excel y columns are (numchildren, id, last, first, married)
valy = first, last, married, numchildren then
keyy = sheetName_y.cells(i,2).value
valy = sheetName_y.cells(i,4).value &";:"&sheetName_y(i,3).value etc.

After the two dictionaries have been filled, you can compare the key/value of one dictionary to the other dictionary.

I have code for doing this when the columns and rows are in the same order for the two spreadsheets. You can even highlight the rows when the values are not identical.

I hope this makes sense.

[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 .