Comparing two excel Sheets whose columns names vary

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